Combining ORDER BY AND UNION in SQL Server

SqlSql ServerSql Order-ByUnion All

Sql Problem Overview

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?

Sql Solutions

Solution 1 - Sql

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

Solution 2 - Sql

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)

Solution 3 - Sql

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

Solution 4 - Sql

SELECT TOP 1 Id as sameColumn,Name FROM Locations 
UNION ALL
SELECT TOP 1 Id as sameColumn,Name FROM Locations ORDER BY sameColumn DESC
Categories
Recommended Sql Solutions
Recommended Sql Server Solutions
Recommended Sql Order-By Solutions

Previous Solution:

How do I use JSON.NET to deserialize into nested/recursive Dictionary and List?

.NetJsonjson.netDeserialization

Next Solution:

Euclidean distance of two vectors

R

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionFaizal BalsaniaView Question on Stackoverflow
Solution 1 - SqlKeithView Answer on Stackoverflow
Solution 2 - SqlDamien_The_UnbelieverView Answer on Stackoverflow
Solution 3 - SqlRichardTheKiwiView Answer on Stackoverflow
Solution 4 - SqlSiddappa WalakeView Answer on Stackoverflow