60

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?

Himanshu
  • 31,810
  • 31
  • 111
  • 133
Faizal
  • 1,693
  • 6
  • 20
  • 24

4 Answers4

87

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
Keith
  • 150,284
  • 78
  • 298
  • 434
  • Keith I am using SQL Server 2005 and when I do as suggested by you I get the following error, The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. When i try the following, `SELECT * FROM ( SELECT * FROM CTEWithNDCNotNull ORDER BY NDC ASC ) AS NDCNotNull UNION SELECT * FROM ( SELECT * FROM CTEWithNDCNull ORDER BY GPI ASC ) AS NDCNull` How do I correct this? –  Jun 27 '13 at 16:55
  • 2
    @peplamb You have to specify `top` after the sub-query `select`. You can't sort a `union` without specifying how many rows you want. – Keith Jun 28 '13 at 06:40
  • 1
    This isn't possible to do without `TOP`? so what is the option if I don't know how many rows I want? – akousmata May 26 '19 at 00:48
  • @akousmata you can use `top (@limit)`. You could probably use rank over but it would be much more complicated and no quicker. This query is fast as long as the column is indexed. – Keith May 26 '19 at 18:16
12
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
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • This works perfactly fine. but why do we need one more select , I mean one extra select makes this confusing. Any reason why union not working without this. – Sharad Mar 07 '23 at 06:37
11

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)

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Good alternate answer, though mine could return one row too if you replace `union all` with `union`. It could be worth comparing them in the query analyser. – Keith Apr 05 '11 at 14:13
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
Rob
  • 26,989
  • 16
  • 82
  • 98
Siddappa Walake
  • 303
  • 5
  • 14
  • 2
    how is this different from existing answers ? – TheGameiswar Aug 01 '17 at 06:48
  • First we need to have same column names to use unions. So i just used same alias in both views for sorting. – Siddappa Walake Aug 01 '17 at 06:53
  • The column is already the same name in the OP's question, you also removed the first `ORDER BY` clause in the first statement, so if `Id` is an int, but not sorted, you will not get the "first" `Id`, just a random one. Suppose your Id column is `9 7 4 8 3 1` Your query will return 9 in both sets because 9 is the first one in the column when you don't sort at all, but also the first one when you sort descending. – akousmata May 26 '19 at 00:53
  • @akousmata, Presuming Id part of index. – Siddappa Walake Jul 30 '19 at 22:48
  • 2
    This is an old answer, but as it is wrong I suggest you delete it anyway in order not to confuse anybody. You have two queries both without a `TOP` clause and no `ORDER BY`. This gets you two rows arbitrarily picked. Then you `UNION ALL` the two queries and order the result. Too late :-) – Thorsten Kettner Nov 17 '19 at 23:57