Understanding the relation between UNION and ORDER BY?

Question

Here are 2 queries -

select * --1
from Employee --2
where HireDate < '7/1/2001' --3
order by HireDate --4

--5 gap   

select top(2) * --6
from Employee --7
where HireDate >= '7/1/2001' --8
order by HireDate --9

I want to do a UNION on them. When i put UNION in 5, why do i get an error?

When, I remove 4 and put UNION in 5, I get a result, but not the same as when I execute the two queries individually. Can you tell me why this happens?

To make this work correctly, I have to remove 4, make derived tables of both queries, put 4 after 9 and then perform a UNION of both derived tables.

@DownVoter - Oh ! Please tell us why you down voted the question. Otherwise, we will think that you meant to click the up button instead :) — sequel.learner, Jan 14 '13 at 07:31
You probably got down voted because your title and tags said this was a group by problem and in fact it was an order by issue. — Ilion, Jan 14 '13 at 08:25

score 2 · Accepted Answer · answered Jan 14 '13 at 07:23

2

remove the ORDER BY before UNION. (it causes Syntax Error)

select *
from Employee 
where HireDate < '7/1/2001' 
UNION
select top(2) * 
from Employee 
where HireDate >= '7/1/2001' 
order by HireDate

the ORDER BY clause takes place after the UNION

answered Jan 14 '13 at 07:23

John Woo

258,903
69
498
492

1

Or to be precise: the `ORDER BY` sorts the *whole* result set. There is no sense in sorting each part of an `UNION`. – Jan 14 '13 at 07:27
1

@a_horse_with_no_name - bingo ! Now it makes sense. I made the above two queries into derived tables, followed by an order by. Then i got the 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. ". Removed the order by at 4 and it worked perfectly. – sequel.learner Jan 14 '13 at 07:36

score 1 · Answer 2 · edited May 23 '17 at 10:26

1

This will help you I guess for your question

Combining ORDER BY AND UNION in SQL Server

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

edited May 23 '17 at 10:26

Community

1
1

answered Jan 14 '13 at 07:27

Sandip Bantawa

2,822
4
31
47

1

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – ajacian81 Jan 14 '13 at 07:47
it feels as if I am taking others credibility on this domain, but then you are right – Sandip Bantawa Jan 14 '13 at 08:02
1

Just cite your sources. ;) – Ilion Jan 14 '13 at 08:26

Understanding the relation between UNION and ORDER BY?

2 Answers2