GROUP BY with multiple fields (SQL Server 2000)

Question

I have a table with 3 columns:

History:

ID  |  xDate  |  xUser

I would like to return the ID, xDate and xUser for the last xDate of each ID.

This is what I've got:

SELECT 
    ID, Last = Max(xDate)
FROM 
    History
GROUP BY 
    ID
ORDER BY 
    Last DESC

As soon as I add the xUser to the SELECT, it stops working.

Any help would be greatly appreciated.

 ID  |  xDate  |  xUser  
 01     2014-1  Joe  
 01     2014-2  Bob  
 01     2014-3  Tom  
 02     2014-1  Joe  
 02     2014-2  Bob  
 02     2014-3  Tom  

Desired results:

 ID  |  xDate  |  xUser  
 01     2014-3  Tom  
 02     2014-3  Tom  

Answer 1

You need to pre-query for each ID, what is the last date... Then from that, re-join to your history table on BOTH components to get the name corresponding to that ID

SELECT 
      H2.ID, 
      H2.xDate,
      H2.Name
   FROM 
      ( select ID, max(xDate) ThisDate
           from History
            Where xdate > '2014-09-01'
           group by ID ) PreCheck
         JOIN History H2
            on PreCheck.ID = H2.ID
           AND PreCheck.ThisDate = H2.xDate
   ORDER BY 
      H2.xDate DESC

Your issue will be if you have multiple entries on a given date unless the dates are really a full date/time for most recent entry.

Also, it would be best to have an index on your table on (ID, xDate)