Combining ORDER BY AND UNION in SQL Server

Question

How can I get first record of a table and last record of a table in one result-set?

This Query fails

SELECT TOP 1 Id,Name FROM Locations ORDER BY Id UNION ALL SELECT TOP 1 Id,Name FROM Locations ORDER BY Id DESC 

Any help?

Answer 1

Put your order by and top statements into sub-queries:

select first.Id, first.Name  from (     select top 1 *      from Locations      order by Id) first union all select last.Id, last.Name  from (     select top 1 *      from Locations      order by Id desc) last 

Answer 2

If you're working on SQL Server 2005 or later:

; WITH NumberedRows as (     SELECT Id,Name,        ROW_NUMBER() OVER (ORDER BY Id) as rnAsc,        ROW_NUMBER() OVER (ORDER BY Id desc) as rnDesc     FROM         Locations ) select * from NumberedRows where rnAsc = 1 or rnDesc = 1 

The only place this won't be like your original query is if there's only one row in the table (in which case my answer returns one row, whereas yours would return the same row twice)