Return value at max date for a particular id

Question

Here's my sql server table

ID       Date       Value 
___      ____       _____
3241     9/17/12    5
3241     9/16/12    100
3241     9/15/12    20
4355     9/16/12    12
4355     9/15/12    132
4355     9/14/12    4
1234     9/16/12    45
2236     9/15/12    128
2236     9/14/12    323
2002     9/17/12    45

This seems like it should be easy to do, but I don't know why I'm stuck. I'd like to select ONLY the max(date) and value at that max(date) for each id. I want to ignore all other dates that aren't the max(date) with respect to each id.

Here's what I'd like the table to look like:

ID       Date       Value 
___      ____       _____
3241     9/17/12    5
4355     9/16/12    12
1234     9/16/12    45
2236     9/15/12    128
2002     9/17/12    45

I tried group by using max(date), but it didn't group anything. I'm not sure what I'm doing wrong. Thanks in advance for the help!

Taryn · Accepted Answer · 2017-06-23T15:15:34.450

23

You can use the following:

select t1.id, t2.mxdate, t1.value
from yourtable t1
inner join
(
  select max(date) mxdate, id
  from yourtable
  group by id
) t2
  on t1.id = t2.id
  and t1.date = t2.mxdate

See Demo

edited Jun 23 '17 at 15:15

answered Sep 17 '12 at 17:58

Taryn

242,637
56
362
405

This is perfect. Thanks bluefeet! – Deron S Sep 17 '12 at 18:19
Nope sorry. It makes complete sense to me. I was initially confused on how to apply the inner join with a pivot, but I wrapped them both in a sub-query and it all worked out. – Deron S Sep 18 '12 at 18:28
@DeronS glad you figured it out. :) – Taryn Sep 18 '12 at 18:34
@bluefeet What if i have a null value in my dates and i would like to include it also ? http://sqlfiddle.com/#!6/01da1 – Hicham Bouchilkhi Jun 23 '17 at 12:30
@Esperadoce I'd recommend [asking a new question](https://stackoverflow.com/questions/ask) with your exact requirements. – Taryn Jun 23 '17 at 15:13
@bluefeet thank you sir for your help i made this post as you recommend https://stackoverflow.com/questions/44755281/return-value-at-max-date-for-a-particular-id-with-null-as-maximum-value-in-t-sql – Hicham Bouchilkhi Jun 26 '17 at 08:15
This comment is also useful for pulling data from log tables, if you use `max(primary_key)`, assuming that primary_key is some kind of auto-increment field and therefore newer log items have higher IDs – Jon Story Apr 08 '21 at 13:13
1

@Taryn -- this is still golden. tysvm – Devin Vyain Apr 13 '22 at 03:35

score 2 · Answer 2 · answered Jun 25 '21 at 17:20

2

I have used this for avoiding join statement

WITH table1 
AS (SELECT
  id,
  Date,
  Value,
  ROW_NUMBER() OVER (PARTITION BY id ORDER BY Date DESC) AS rn
FROM yourtable)
SELECT
  *
FROM table1
WHERE rn = 1

answered Jun 25 '21 at 17:20

nvsk. avinash

581
1
5
10

score 1 · Answer 3 · answered Sep 17 '12 at 17:58

1

This would give you what you need:

SELECT 
    m.ID,
    m.Date,
    m.Value
FROM 
    myTable m
    JOIN (SELECT ID, max(Date) as Date FROM myTable GROUP BY ID) as a
    ON m.ID = a.ID and m.Date = a.Date

answered Sep 17 '12 at 17:58

Vikdor

23,934
10
61
84

score 0 · Answer 4 · answered Mar 12 '19 at 23:36

You haven't specified your SQL implementation, but something like this should work:

Note that the op didn't specifically ask to use max(), just to get the id, value at the max[imum] date.

TSQL:

select top 1 ID, Date, Value from yourtable order by Date DESC;

Not TSQL, has to support limit: (Not tested.)

select ID, Date, Value from yourtable order by Date DESC limit 1,1;

Return value at max date for a particular id

4 Answers4

Linked