return only one row for each id field that meets the criteria

[sknyppy]

I have a query that returns the following information.

I need it to return only one row for each id field that meets the criteria, which should be the 2 rows(ids: 20,37).

QUERY:
----------------------------------------------
SELECT id, contentid, dateDisplayEnd
FROM dbo.tbl_menu_content
WHERE (dateDisplayEnd > GETDATE())
AND (contentQueueID = 2)
ORDER BY id, dateDisplayEnd
----------------------------------------------


RESULTS:
id contentid dateDisplayEnd
----------------------------------------------
20 100 8/21/2004 12:00:01 AM
20 102 8/21/2004 12:00:01 AM
20 103 8/21/2004 12:00:01 AM
20 104 8/21/2004 12:00:01 AM
20 108 8/21/2004 12:00:01 AM
20 119 8/31/2004 12:00:01 AM
37 106 8/24/2004 12:00:01 AM
----------------------------------------------

Thanks..

 

[SQLSister]

So how do you determine whic of the multiple rows with avlaue of 20 you want returned?

Questions about posting. See faq183-874

 

[thendrickson]

SELECT top 1 id, contentid, dateDisplayEnd
FROM dbo.tbl_menu_content
WHERE (dateDisplayEnd > GETDATE())
AND (contentQueueID = 2)
ORDER BY contentid id, dateDisplayEnd Asc
will give you only the record with an id of 20 and the contentid of 100 as well as the record with the id of 37.

Or you can order by desc.

SQL Sister is correct though. Without knowing what criteria you are using to select which of the mutiple rows you want it is impossibler to give a accurate answer, but I have had to use this tecnique several times (when it fits the needs)