Q

Script to select specific record from a multiple-records table

Expert Greg Low explains how to select the correct records when they share an ID.

I want to write SQL. There are multiple records in a table with the same ID and I want to select the record with the second most recent date. If there is only one record for that, I would want to select that as well. I would appreciate your answer.

CREATE TABLE TestTable ( ID int, TestDate datetime )

GO
INSERT TestTable VALUES(1,'20051231')
INSERT TestTable VALUES(1,'20051230')
INSERT TestTable VALUES(1,'20051230')
INSERT TestTable VALUES(1,'20051229')
INSERT TestTable VALUES(2,'20051230')
INSERT TestTable VALUES(3,'20051229')
INSERT TestTable VALUES(3,'20050101')
GO

The query might be:

SELECT ID,TestDate
FROM TestTable AS tt
WHERE (SELECT COUNT(*)
FROM TestTable AS tc
WHERE tt.ID = tc.ID
AND tt.TestDate < tc.TestDate) = 1
UNION
SELECT ID,MIN(TestDate)
FROM TestTable
GROUP BY ID
HAVING COUNT(ID) = 1
ORDER BY ID

This was first published in January 2006

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close