Select info from table where row has max date

Solution 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).

Solution 2:

You can use a window MAX() like this:

SELECT
  *, 
  max_date = MAX(date) OVER (PARTITION BY group)
FROM table

to get max dates per group alongside other data:

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

Using the above output as a derived table, you can then get only rows where date matches max_date:

SELECT
  group,
  date,
  checks
FROM (
  SELECT
    *, 
    max_date = MAX(date) OVER (PARTITION BY group)
  FROM table
) AS s
WHERE date = max_date
;

to get the desired result.

Basically, this is similar to @Twelfth's suggestion but avoids a join and may thus be more efficient.

You can try the method at SQL Fiddle.

Solution 3:

Using an in can have a performance impact. Joining two subqueries will not have the same performance impact and can be accomplished like this:

SELECT *
  FROM (SELECT msisdn
              ,callid
              ,Change_color
              ,play_file_name
              ,date_played
          FROM insert_log
         WHERE play_file_name NOT IN('Prompt1','Conclusion_Prompt_1','silent')
        ORDER BY callid ASC) t1
       JOIN (SELECT MAX(date_played) AS date_played
               FROM insert_log GROUP BY callid) t2
         ON t1.date_played = t2.date_played

Solution 4:

SELECT distinct
  group, 
  max_date = MAX(date) OVER (PARTITION BY group), checks
FROM table

Should work.