value for the max date group by id

  • Hi everyone,

    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?

    R

  • You've got that many points and you can't post create table and insert scripts? =(

    Anyway, do everyone a favor and post them so they don't have to waste their time typing...

    use tempdb;
    go

    CREATE TABLE #test (theDate DATE, ID int, val int);
    go
    INSERT INTO #test VALUES
    ('20210109', 12, 234),
    ('20210131', 12, 456),
    ('20210225', 23, 4567),
    ('20210228', 23, 567),
    ('20210315', 56, 456),
    ('20210326', 45, 3435),
    ('20210328', 45, 4567),
    ('20210327', 56, 12345);

    Once you have that part, the answer isn't that hard...

    SELECT t2.ID
    , md.MaxDate
    , t2.val
    FROM #test t2
    INNER JOIN
    -- I need the max date for that id
    (
    SELECT t.id,
    MaxDate = MAX(t.theDate)
    FROM #test t
    GROUP BY id
    ) md
    ON (t2.ID = md.ID AND t2.theDate = md.MaxDate)
    ORDER BY t2.id;

    (I mean, I could do it... so it can't be that hard, right?)

  • I would say the modern approach to doing this is using ROW_NUMBER(), partly because of its inherent efficiency:

    SELECT theDate, ID, val
    FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY theDate DESC) AS row_num
    FROM #test
    ) AS derived
    WHERE row_num = 1

     

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Thanks for replying everyone! Sorry I should have posted create scrip as well

  • I have another requirement:

    CREATE TABLE #test (theDate DATE, value int, flag int);

    select * from #test

    INSERT INTO #test VALUES

    ('20210109', 12, 1),

    ('20210109', 12, 0),

    ('20210131', 12, 0),

    ('20210131', 12, 0),

    ('20210131', 12, 0),

    ('20210225', 23, 1),

    ('20210225', 23, 0),

    ('20210228', 23, 1),

    ('20210315', 56, 1),

    ('20210326', 45, 1),

    ('20210328', 45, 1),

    ('20210327', 56, 1),

    ('20210328', 45, 0),

    ('20210327', 56, 0);

     

    for above  dataset, I would like to select all records with 1 for the same date and If I don't see a one, select zero. Is it possible to write this in one query.

    final result

    '20210109', 12, 1

    '20210131', 12, 0

    '20210225', 23, 1

     

    Thanks.

  • [Use ROW_NUMBER() partly because of inherent efficiency] And partly because it's so easy to adapt:

    SELECT theDate, value, flag
    FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY theDate ORDER BY flag DESC) AS row_num
    FROM #test
    ) AS derived
    WHERE row_num = 1
    ORDER BY theDate

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply