CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Francis Ducharme
Francis Ducharme

Reputation: 4987

How to get a specific row as first result, then the rest of the result set

I wonder if it's possible to have a query that will return a result set with a specific row as first element, then the rest, ordered by a specific row.

I tried this:

declare @testId int

select @testId = (select TestID from Tests Where FileID='111' AND TestDate='2010-01-01')

select * from Tests where TestId=@TestId

union

select * from Tests where TestId <> @TestId and FileID='111' order by TestDate desc

That doesn't really work as it outputs a regular SELECT ordered by TestDate, descending.

Any ideas ?

Thanks!

EDIT: This query is used to feed a .NET DataTable.

Upvotes: 2

Views: 1263

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460380

You can use CASE in the ORDER BY:

SELECT * 
FROM Tests 
Where FileNumber = '111' 
ORDER BY CASE WHEN AppointmentDT='2010-01-01' THEN 0 ELSE 1 END ASC 
    , AppointmentDT DESC

Upvotes: 4

Related Questions