CODEWITHSUNDEEP
sql-serversql-server-2014union-all
ahmed abdelqader
ahmed abdelqader

Reputation: 3560

Using Top with Union all

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 !

Upvotes: 1

Views: 1020

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

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.

Upvotes: 3

Related Questions