MS Query SQL

[Remoladen]

HiI have a table like the one below Id Init Date x y z 1 ABA 01-06-2011 6 6 6 2 ABC 01-12-2009 7,5 7,5 7,5 3 ABC 03-03-2012 7,5 7,5 7,5 4 ABC 01-02-2012 0 0 0 5 ABO 20-10-2008 7,5 7,5 7,5 6 ABRA 01-01-2012 0 0 0 7 ABRA 16-02-2009 0 0 0 8 ABRA 12-10-2011 0 0 0 9 ABRA 05-04-2005 0 0 0 10 ABRA 02-02-1998 0 0 0 11 ABRA 15-07-2011 0 0 0 12 ABRA 01-10-2010 0 0 0 13 ABRA 01-03-1987 0 0 0 14 ABRA 01-01-2012 0 0 0 15 ABT 02-05-2012 7,5 7,5 7,5 16 ABT 01-01-2012 7,5 7,5 7,5 17 ADRF 01-05-2001 0 0 0 18 ADRF 24-04-2012 7,5 7,5 7,5 19 AEN 01-01-2013 6,5 6,5 6,5 20 AFO 01-06-2012 7,5 7,5 7,5 21 AFO 01-12-2010 7,5 7,5 7,5 22 AGA 03-03-2009 7,5 7,5 7,5 23 AGA 01-02-2013 0 0 0 24 AGM 20-10-2009 7,5 7,5 7,5 What I would like to do is to select the row for each "Init" with the date closest to Now, but not in the future (hope it makes sense) I've tried with: Select Id, Init, min(getdate() - date), x, y, zFrom tableGroup By InitHaving date<getdate It doesn't work because Id, x, y, z isn't "a part of aggregatefunction" ! Any good ideas ? Please

Edited January 8, 2013 by Remoladen

[justsomeguy]

Group by the other fields also. You can also sort the results to get the one you're looking for first.