SELECT most recent dated row by ID

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SELECT most recent dated row by ID

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2012-05-22 : 16:07:42

This is probably the simplest thing, but I am not getting it. I have a simplified query here where I want the whole row of the most recent (MAX) date for each P_ID. I have a whole script here complete with a basic select. I know there should be a sub-query or join someplace here:

CREATE TABLE #t (
DayKey INT,
P_ID VARCHAR(8),
Seq VARCHAR(5)
)

INSERT INTO #t([DayKey], [P_ID], [Seq])
SELECT 20110101, N'1', N'Rec1' UNION ALL
SELECT 20110102, N'1', N'Rec2' UNION ALL
SELECT 20110512, N'1', N'Rec3' UNION ALL
SELECT 20120522, N'1', N'Rec4' UNION ALL
SELECT 20120522, N'2', N'Rec1' UNION ALL
SELECT 20110301, N'3', N'Rec1' UNION ALL
SELECT 20110621, N'3', N'Rec2' UNION ALL
SELECT 20120521, N'4', N'Rec1' UNION ALL
SELECT 20110704, N'5', N'Rec1' UNION ALL
SELECT 20120813, N'5', N'Rec2' UNION ALL
SELECT 20120902, N'6', N'Rec1' UNION ALL
SELECT 20120915, N'7', N'Rec1' UNION ALL
SELECT 20111011, N'7', N'Rec2' UNION ALL
SELECT 20120522, N'7', N'Rec3' UNION ALL
SELECT 20111111, N'7', N'Rec4'

SELECT [DayKey]
      ,[P_ID]
      ,[Seq]
FROM #t

DROP TABLE #t

The results will be inserted into a table that has no DayKey and must have a unique P_ID, so there can only be one of each P_ID.

Duane

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2012-05-22 : 16:16:04

SELECT t1.*
FROM #t t1
JOIN (SELECT MAX([DayKey]) maxkey,[P_ID] FROM #t GROUP BY P_ID) t2 ON t1.DayKey = t2.maxkey AND t1.P_ID = t2.P_ID

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2012-05-22 : 17:09:54

Thank you for the help. While you came up with your response, I came up with this, but I am not sure if it really should be an INNER JOIN or a RIGHT JOIN. My query seems to work, though, and I am going to try yours. But here is my solution:

SELECT #t.[DayKey]
      ,#t.[P_ID]
      ,#t.[Seq]
FROM #t
RIGHT JOIN
(
SELECT MAX(DayKey) DayKey, P_ID
FROM #t
GROUP BY P_ID
) AS p ON p.DayKey = #t.DayKey
AND p.P_ID = #t.P_ID
DROP TABLE #t

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources