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

Possible duplicate http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql?rq=1 — Tom, Oct 17 '13 at 17:04
Possible duplicate of [SQL Server: SELECT only the rows with MAX(DATE)](https://stackoverflow.com/questions/7118170/sql-server-select-only-the-rows-with-maxdate) — feetwet, Nov 18 '17 at 18:45

Twelfth · Accepted Answer · 2017-10-31T19:43:16.977

182

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

edited Oct 31 '17 at 19:43

answered Oct 17 '13 at 17:11

Twelfth

7,070
3
26
34

1

Typo in `Select group,max_date,check` column name is "checks". got me confused a little what check is doing here. – Michael B. Nov 23 '16 at 14:51
1

+1, My table =>( ID, group_ID, DateColumn), I need to get the date value with Max(ID) with respect to group_ID. It workd! – Pranesh Janarthanan Apr 21 '17 at 14:06

score 53 · Answer 2 · edited May 23 '17 at 11:55

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.

score 5 · Answer 3 · edited Jan 28 '20 at 23:51

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

score 4 · Answer 4 · answered Nov 28 '18 at 02:55

4

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

Should work.

answered Nov 28 '18 at 02:55

aha

49
2

1

Its not an answer. Please tell more informations about your query. – Adrian Preuss Jan 19 '21 at 23:19
At this point, years after original question, this should be the top accepted answer. The WINDOW function is more efficient than the join, plus adding the DISTINCT gets the correct answer for the OP's questions, just add the WHERE checks>0. – Riccarr Nov 01 '22 at 20:42
@AdrianPreuss ... to answer your question, this is a basic DISTINCT select on the table. The table fields "group" & "checks" are unique, and the use of the window function gets max "date" for that group ("group" & "checks"). – Riccarr Nov 01 '22 at 20:45

Ron · Answer 5 · 2023-04-12T03:32:48.853

SELECT *
INTO #temp
FROM
(
    VALUES
        (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)
) x ([group], date, cash, checks);


-- Method 1
SELECT distinctgroup.[group],
       max_date.date,
       max_date.cash,
       max_date.checks
FROM
(SELECT DISTINCT [group] FROM #temp) distinctgroup
    CROSS APPLY
(
    SELECT TOP 1
           [group],
           date,
           cash,
           checks
    FROM #temp t
    WHERE distinctgroup.[group] = t.[group]
    ORDER BY date DESC
) max_date;



-- Method2

SELECT ot.[group],
        ot.date,
       ot.cash,
       ot.checks
       
FROM 
(SELECT  it.[group],
         it.date,
         it.cash,
         it.checks, ROW_NUMBER() OVER (PARTITION BY [group] ORDER BY date DESC) AS rownum 
FROM #temp it) ot
WHERE ot.rownum=1





DROP TABLE #temp;

Select info from table where row has max date

5 Answers5

Linked