value for the max date group by id

Question

Post reply

value for the max date group by id

rohitkocharda

Say Hey Kid

Points: 687
More actions
June 24, 2021 at 10:15 pm

#3900222

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

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

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

pietlinden SSC Guru Points: 63509 More actions · Answer 1

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

ScottPletcher SSC Guru Points: 101121 More actions · Answer 2

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

rohitkocharda Say Hey Kid Points: 687 More actions · Answer 3

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

rohitkocharda Say Hey Kid Points: 687 More actions · Answer 4

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

('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.

This reply was modified 3 years, 5 months ago by rohitkocharda.

ScottPletcher SSC Guru Points: 101121 More actions · Answer 5

[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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".