Selecting Unique Items in DB based on Date

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

General SQL Server Forums

New to SQL Server Programming

Selecting Unique Items in DB based on Date

Author

Topic

Velnias
Yak Posting Veteran

58 Posts

Posted - 2008-06-26 : 11:31:19

Hey all,

In a table I have two 3 columns

ID / groupCode / Date

Date in this might look like

1 65 21/04/2008
2 23 21/04/2008
3 65 23/04/2008
4 23 19/04/2008
5 65 18/04/2008
6 21 21/04/2008

What I want my SQL query to do is select all the Unqiue groupCodes record based on most recent date.

For example
3 65 23/04/2008
2 23 21/04/2008
6 21 21/04/2008

Any ideas ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-26 : 11:34:09

Select t1.* from table t1
where date=(select max(date) from table where groupcode=t1.groupcode)

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-26 : 11:35:24

[code]SET dateformat dmy
DECLARE @TABLE TABLE
(
ID int,
groupCode int,
[Date] datetime
)

INSERT INTO @TABLE
SELECT 1, 65, '21/04/2008' UNION ALL
SELECT 2, 23, '21/04/2008' UNION ALL
SELECT 3, 65, '23/04/2008' UNION ALL
SELECT 4, 23, '19/04/2008' UNION ALL
SELECT 5, 65, '18/04/2008' UNION ALL
SELECT 6, 21, '21/04/2008'

SELECT ID, groupCode, [Date]
FROM (
SELECT ID, groupCode, [Date],
row_no = row_number() OVER (PARTITION BY groupCode ORDER BY [Date] DESC)
FROM @TABLE
) t
WHERE t.row_no = 1

/*
ID groupCode Date
----------- ----------- ------------------------------------------------------
6 21 2008-04-21 00:00:00.000
2 23 2008-04-21 00:00:00.000
3 65 2008-04-23 00:00:00.000

(3 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-26 : 11:37:21

Not sure if OP uses SQL Server 2005

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-26 : 11:38:31

we will know soon enough

KH
[spoiler]Time is always against us[/spoiler]

Velnias
Yak Posting Veteran

58 Posts

Posted - 2008-06-26 : 11:50:47

Hey thanks for the replies...

I did forget to mention there where more columns in that table :P I tried

SELECT id, dateAdded, siebelObjectId, accountNumber, accountName, fileTypeId, fileTypeName, intermediaryCode, pdfContent, completionReportDate,
statusId
FROM tblPDFFiles
WHERE (completionReportDate =
(SELECT MAX(completionReportDate) AS Expr1
FROM tblPDFFiles AS tblPDFFiles_1
WHERE (intermediaryCode = tblPDFFiles.intermediaryCode))) and fileTypeId = 3

But I only got 2 out of 3 Files cuz one record which is fileTypeId = 1 had the same intermediaryCode

Velnias
Yak Posting Veteran

58 Posts

Posted - 2008-06-26 : 11:57:35

This guy seems to work nicely

SELECT id, intermediaryCode, completionReportDate
FROM (SELECT id, intermediaryCode, completionReportDate, row_no = row_number() OVER (PARTITION BY intermediaryCode
ORDER BY completionReportDate DESC)
FROM tblPDFFiles
WHERE fileTypeId = 3) t
WHERE t .row_no = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources