sql - 在 SQL Server 中组合 ORDER BY 和 UNION

Question

如何获取一个结果集中表的第一条记录和最后一条记录？

此查询失败

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

有什么帮助吗？

Answer 1

将 order by 和 top 语句放入子查询中:

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