Select info from table where row has max date

Question

My table looks something like this:

group    date      cash  checks   1    1/1/2013     0      0   2    1/1/2013     0      800   1    1/3/2013     0      700   3    1/1/2013     0      600   1    1/2/2013     0      400   3    1/5/2013     0      200 

-- Do not need cash just demonstrating that table has more information in it

I want to get the each unique group where date is max and checks is greater than 0. So the return would look something like:

group    date     checks   2    1/1/2013    800   1    1/3/2013    700   3    1/5/2013    200 

attempted code:

SELECT group,MAX(date),checks     FROM table     WHERE checks>0     GROUP BY group     ORDER BY group DESC 

problem with that though is it gives me all the dates and checks rather than just the max date row.

using ms sql server 2005

Answer 1

SELECT group,MAX(date) as max_date FROM table WHERE checks>0 GROUP BY group 

That works to get the max date..join it back to your data to get the other columns:

Select group,max_date,checks from table t inner join  (SELECT group,MAX(date) as max_date FROM table WHERE checks>0 GROUP BY group)a on a.group = t.group and a.max_date = date 

Inner join functions as the filter to get the max record only.

FYI, your column names are horrid, don't use reserved words for columns (group, date, table).