Combining ORDER BY AND UNION in SQL Server

sql sql-server sql-order-by union-all
Faizal Balsania picture Faizal Balsania · Apr 5, 2011 · Viewed 95.2k times · Source

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

Keith picture Keith · Apr 5, 2011

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

Related questions

inner join and union all using order by

I have this query: SELECT B.IMAGE_ID as image_id_fav,I.Image_Path as image_path_fav FROM Buddies B INNER JOIN @favDT F ON F.favorite_id = B.Reg_ID and b.favorite_id=@regID INNER JOIN …

Read More
How to update and order by using ms sql

Ideally I want to do this: UPDATE TOP (10) messages SET status=10 WHERE status=0 ORDER BY priority DESC; In English: I want to get the top 10 available (status=0) messages from the DB and lock them (status=10). A message with a higher …

Read More
Custom order by in SQL server like P, A, L, H

Not ASC or DESC.... Order by custom... I have tried using case but not successfully SELECT * FROM Customers ORDER BY case country when 'P' then 1 … This is what I want:

Read More