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.
CREATE TABLE TestTable ( ID int, TestDate datetime )
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
By submitting your personal information, you agree to receive emails regarding relevant products and special offers from TechTarget and its partners. You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.
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
Dig Deeper
PRO+
Content
Find more PRO+ content and other member only offers, here.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our SQL Server experts
View all SQL Server questions and answers
0 comments
Oldest Newest