Group By?greenspun.com : LUSENET : SQL Server Database Administration : One Thread |
Is there a generic way of retrieving unique rows based on a max or min of another column in the set? For example, I may have several rows per foreign key with different dates in a column outside the key. I may want to bring back the keys of the 'latest' row in each group of foreign keys.FK Date Col2 ----- -------- --------- Donna 3/2/90 Stephanie Donna 3/29/93 Matthew Donna 12/18/95 Emily Karen 5/9/76 Scott Karen 4/17/78 Ben
I want only the Emily and Ben rows (and possibly other columns).
-- Anonymous, March 23, 2000
SELECT FK,Date,Col2 FROM Table_Whatever WHERE Date = (SELECT max (date) FROM table_whatever WHERE FK = Donna)SELECT FK,Date,Col2 FROM Table_Whatever WHERE Date = (SELECT max (date) FROM table_whatever WHERE FK = Karen)
OR
SELECT FK,Date,Col2 FROM Table_Whatever WHERE Date = (SELECT max (date) FROM table_whatever WHERE FK = Donna OR FK = Karen)
(I'm not too sure about the second one but I think it would work)
-- Anonymous, March 24, 2000
Troy,Use a correlated subquery like so:
SELECT *
FROM whatever as T1
WHERE DateColumn = (select max(DateColumn)
from whatever as T2
where T1.FK = T2.FK)
(Pardon the extra spacing, it is needed for proper presentation on the forum. I have also renamed Date to DateColumn to avoid the use of the reserved word 'Date'.)
Hope this helps,
Eric
-- Anonymous, March 26, 2000