Complex query help needed....

[steven.fafel]

I have been working with SQL for a while...but I am stumped. I can not
seem to get my arms around this query....can anyone help...

Here it is:

Table = 12 rows, 4 columns (id, name, amount, date)
row1 = 771, "steve", $50.00, "01/01/2005"
row2 = 772, "steve", $100.00, "01/11/2005"
row3 = 773, "steve", $200.00, "01/11/2005"
row4 = 774, "dave", $300.00, "01/01/2005"
row5 = 775, "dave", $400.00, "01/12/2005"
row6 = 776, "dave", $500.00, "01/12/2005"
row7 = 777, "mike", $600.00, "01/01/2005"
row8 = 778, "mike", $700.00, "01/13/2005"
row9 = 789, "mike", $800.00, "01/13/2005"
row10 = 790, "chuck", $900.00, "01/01/2005"
row11 = 791, "chuck", $950.00, "01/14/2005"
row12 = 792, "chuck", $975.00, "01/14/2005"

I need a query that returns (1) ONE ROW PER NAME based on the MOST
RECENT DATE and returns the correct corresponding information. The
keys to this question are the following:
1. The query needs to return ONE ROW PER NAME
2. I do not want to use a First() function (in MS Access)
3. Even though (2) two DATE for each NAME are the same, i want the
query to return one record and whatever record it returns, i have to be
able to have all the corresponding records (id, name, amount, and
date). I recorgnize that the DATE is ambiguous and that SQL may return
one or the other...but that is ok.
4. The return set should include (4) four rows

Any help with this would be thoroughly appreciated...

[Thomas R. Hummel]

Assuming that your id column is unique and does not allow NULL values,
the SQL below should return a single record per name, using the record
with the latest date and in this case if the dates are identical then
it will return the record with the greater id.

SELECT T1.id, T1.name, T1.amount, T1.date
FROM Test T1
LEFT OUTER JOIN Test T2 ON T2.name = T1.name
AND ((T2.date > T1.date) OR (T2.date = T1.date
AND T2.id > T1.id))
WHERE T2.id IS NULL
-Tom.