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?

回答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

回答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)

回答3:

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

回答4:

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