Combining ORDER BY AND UNION in SQL Server
Put your order by and top statements into sub-queries:
select first.Id, first.Name from ( select top 1 * from Locations order by Id) firstunion allselect last.Id, last.Name from ( select top 1 * from Locations order by Id desc) last
select * from (SELECT TOP 1 Id,Name FROM Locations ORDER BY Id) XUNION ALLSELECT TOP 1 Id,Name FROM Locations ORDER BY Id DESC
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 = 1The 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)