Value for the max date group by id

Question

I have data in my table as below

date                       ID            value
20210109              12           234
20210131              12        456
20210225             23          4567
20210228             23        567
20210315            56      456
20210326           45      3435
20210328          45       4567
20210327            56      12345

My result should be

date                       ID            value
20210131              12        456
20210228             23        567
20210328          45       4567
20210327            56      12345

There could be more ID values for each month but I need the value for the max date for that (id, maxdate). What is the most efficient way to write this query?

Or this?: [Select info from table where row has max date](https://stackoverflow.com/questions/19432913/select-info-from-table-where-row-has-max-date/43963149) — pwilcox, Jun 24 '21 at 22:42

score 1 · Answer 1 · edited Jun 24 '21 at 22:38

1

You can use row_number():

select t.*
from (select t.*,
             row_number() over (partition by id, year(date), month(date) order by date desc) as seqnum
      from t
     ) t
where seqnum = 1;

edited Jun 24 '21 at 22:38

Dale K

21,987
13
41
69

answered Jun 24 '21 at 22:38

Gordon Linoff

1,198,228
53
572
709

John Cappelletti · Answer 2 · 2021-06-28T17:17:52.957

0

Just another option via WITH TIES

Example

Select Top 1 with ties * 
 From  YourTable
 Order By row_number() over (partition by left(date,6),id order by date desc)

Results

date        ID  value
20210131    12  456
20210228    23  567
20210328    45  4567
20210327    56  12345

Note:

If your DATE column is actually a date and not an INT or string use left(date,7) in the partition by

edited Jun 28 '21 at 17:17

answered Jun 24 '21 at 22:49

John Cappelletti

71,300
6
42
62

1

Thanks John! Using "ties" is exactly what I wanted here. date column is type date so using left(date,7) give me exactly what I wanted. Thank you! – Rohitk Jun 24 '21 at 23:07

score 0 · Answer 3 · edited Jun 25 '21 at 01:07

0

Best way to handle these using windows function and CTE or Derived tables.

Here is one way to do so.

SELECT 
    A.id
    , A.[date]
    , A.[value] 
FROM (
    SELECT
        RANK() OVER ( PARTITION BY  id ORDER BY [date] DESC ) AS rank_
        , id
        , date
        , value
    FROM data_max
) AS A
WHERE A.rank_ = 1

edited Jun 25 '21 at 01:07

Dale K

21,987
13
41
69

answered Jun 25 '21 at 00:01

Gudwlk

837
8
10

Can yo explain where it does not show correct result? @DaleK. AND Please let the person who post the question to have a look first – Gudwlk Jun 25 '21 at 00:52
1

My mistake - apologies. – Dale K Jun 25 '21 at 01:06
@Rohitk can you check this answer? – Gudwlk Jun 25 '21 at 01:42
1

Well check all the answers - they all produce the desired results. – Dale K Jun 25 '21 at 01:43
ROW_NUMBER() and RANKNGS can use for these kind of questions. – Gudwlk Jun 25 '21 at 01:55

Value for the max date group by id

3 Answers3