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

Answers

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


Moderation questions? read the FAQ