Ask the Expert

Script to select specific record from a multiple-records table

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: