1

In SQL Server 2014, I am using the Northwind as sample database, and Orders table.

For Selecting the First row:

select top 1 orderID, CustomerID,EmployeeID,OrderDate,RequiredDate 
from orders

Output:

orderID CustomerID  EmployeeID  OrderDate   RequiredDate
10248   VINET   5   1996-07-04 00:00:00.000 1996-08-01 00:00:00.000

For Selecting The last row:

select top 1 orderID, CustomerID,EmployeeID,OrderDate,RequiredDate 
from orders order by orderID desc

Output:

orderID CustomerID  EmployeeID  OrderDate   RequiredDate
11077   RATTC   1   1998-05-06 00:00:00.000 1998-06-03 00:00:00.000

Lets combine The First and Last Rows via using Union ALL:

select top 1 orderID, CustomerID,EmployeeID,OrderDate,RequiredDate 
from orders
union all
select top 1 orderID, CustomerID,EmployeeID,OrderDate,RequiredDate 
from orders order by orderID desc

Output:

orderID CustomerID  EmployeeID  OrderDate   RequiredDate
10248   VINET   5   1996-07-04 00:00:00.000 1996-08-01 00:00:00.000
10248   VINET   5   1996-07-04 00:00:00.000 1996-08-01 00:00:00.000

So Why the last result doesn't show the data as expected.

I know for getting the first and last row, use the next one:-

select top 1 orderID, CustomerID,EmployeeID,OrderDate,RequiredDate 
from orders
union all
Select * from
(
select top 1 orderID, CustomerID,EmployeeID,OrderDate,RequiredDate 
from orders order by orderID desc ) a

Output:-

orderID CustomerID  EmployeeID  OrderDate   RequiredDate
10248   VINET   5   1996-07-04 00:00:00.000 1996-08-01 00:00:00.000
11077   RATTC   1   1998-05-06 00:00:00.000 1998-06-03 00:00:00.000

But I am still confused why I should but second select into derived table !

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
ahmed abdelqader
  • 3,409
  • 17
  • 36
  • 1
    They're different because you aren't using an `ORDER BY` on the first query. Without an `ORDER BY` the `TOP 1` result is not guaranteed to be the same every time. If you're wanting to get the first result, you need to do an `ORDER BY orderID ASC`. – Siyual Feb 27 '17 at 19:28
  • @Siyual, it is not working , if I used `Order by OrderID ASC` in the First query, I am getting: `Incorrect syntax near the keyword 'union'. ` – ahmed abdelqader Feb 27 '17 at 19:36

1 Answers1

3

This 

select top 1 orderID, CustomerID,EmployeeID,OrderDate,RequiredDate 
from orders
union all
select top 1 orderID, CustomerID,EmployeeID,OrderDate,RequiredDate 
from orders order by orderID desc

actually works as:

(select top 1 orderID, CustomerID,EmployeeID,OrderDate,RequiredDate 
from orders
union all
select top 1 orderID, CustomerID,EmployeeID,OrderDate,RequiredDate 
from orders) order by orderID desc

Because the order by is evaluated at the very last in a union unless it is wrapped inside a subquery.

You already know the workaround, so I am not mentioning it.

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76