Second earliest reference - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Second earliest reference

Author

Topic

Humate
Posting Yak Master

101 Posts

Posted - 2007-12-19 : 14:32:33

Hi all,

I am struggling with a query at the moment where I want to find the second earliest (lowest number) ID for each record.

At the moment I have the following code to find the earliest records, just not sure how to get the second earliest?

SELECT ID2, ID
FROM dbo.info
WHERE (ID2 =
(SELECT MIN(ID2) AS Expr1
FROM dbo.info AS y
WHERE (ID = info.ID)))
ORDER BY ID

I have made a few attempts using TOP command, but not having much luck.

Thanks in advance
Humate

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 15:13:03

[code]SELECT ID2,
ID
FROM (
SELECT ID2,
ID,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID2) AS RecID
FROM dbo.Info
) AS d
WHERE RecID = 2[/code]

E 12°55'05.25"
N 56°04'39.16"