CODEWITHSUNDEEP
sqlsql-serversql-order-byunion-all
Faizal
Faizal

Reputation: 1833

Combining ORDER BY AND UNION in SQL Server

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?

Upvotes: 65

Views: 139305

Answers (4)

Siddappa Walake
Siddappa Walake

Reputation: 323

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

Upvotes: 4

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

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)

Upvotes: 11

Keith
Keith

Reputation: 155622

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

Upvotes: 92

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

select * from (
SELECT TOP 1 Id,Name FROM Locations ORDER BY Id) X
UNION ALL
SELECT TOP 1 Id,Name FROM Locations ORDER BY Id DESC

Upvotes: 12

Related Questions