maintain order of two queries after union

Question

1.00/5 (1 vote)

See more:

SQL

(SELECT sp.PK as swithPersonId, sp.PTPK, emp.Employee_Name, emp.PK, pro.Name, sp.SwitchDate, sp.Half from switch_person sp
            INNER JOIN projects pro
            ON sp.PTPK = pro.PK
            INNER JOIN employee emp
            ON sp.EmployeeNamePK = emp.PK
            WHERE pro.TeamLead = 33
            AND sp.SwitchDate = '2015-6-25'
            AND sp.Half IN (1,2)
            AND sp.LeadBy <> sp.EmployeeNamePK
            ORDER BY pro.PK,emp.PK, sp.Half)

SQL

(SELECT sp.PK as swithPersonId, sp.PTPK, emp.Employee_Name, emp.PK, pro.Name,        sp.SwitchDate, sp.Half from switch_person sp
 INNER JOIN projects pro
 ON sp.PTPK = pro.PK
 INNER JOIN employee emp
 ON sp.EmployeeNamePK = emp.PK
 WHERE sp.SwitchDate = '2015-6-25'
 AND sp.Half IN (1,2)
 AND sp.LeadBy = sp.EmployeeNamePK
 ORDER BY pro.PK,emp.PK, sp.Half )

these two query I am going to combine but I want that order of the result should not change. means first query order and second query order don't mingled. using this and it happening ;(

SQL

(SELECT sp.PK as swithPersonId, sp.PTPK, emp.Employee_Name, emp.PK, pro.Name, sp.SwitchDate, sp.Half from switch_person sp
            INNER JOIN projects pro
            ON sp.PTPK = pro.PK
            INNER JOIN employee emp
            ON sp.EmployeeNamePK = emp.PK
            WHERE pro.TeamLead = 33
            AND sp.SwitchDate = '2015-6-25'
            AND sp.Half IN (1,2)
            AND sp.LeadBy <> sp.EmployeeNamePK
            ORDER BY pro.PK,emp.PK, sp.Half)
            union all
           (SELECT sp.PK as swithPersonId, sp.PTPK, emp.Employee_Name, emp.PK, pro.Name, 		sp.SwitchDate, sp.Half from switch_person sp
            INNER JOIN projects pro
            ON sp.PTPK = pro.PK
            INNER JOIN employee emp
            ON sp.EmployeeNamePK = emp.PK
            WHERE sp.SwitchDate = '2015-6-25'
            AND sp.Half IN (1,2)
            AND sp.LeadBy = sp.EmployeeNamePK
            ORDER BY pro.PK,emp.PK, sp.Half )

how to maintain the order of both query separately

Posted 26-Jun-15 1:29am

Muhamad Faizan Khan

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Answer 1 · 2015-06-26T01:53:00

Solution 1

All you need to do is add another field onto your queries. For example:

SQL

SELECT field1, field2, ... 1 AS OrderBy
UNION ALL
SELECT field1, field2, ... 2 AS OrderBy
ORDER BY OrderBy

That should get you through it.

Posted 26-Jun-15 1:53am

ZurdoDev

Comments

Muhamad Faizan Khan 26-Jun-15 8:00am

did not work

ZurdoDev 26-Jun-15 8:15am

It will work so you must have done something different.

Tomas Takac · Answer 2 · 2015-06-26T01:53:00

Add a constant to your query which will be unique for the query:

SQL

SELECT 1 as queryId, sp.PK as swithPersonId, ...
UNION ALL
SELECT 2 as queryId, sp.PK as swithPersonId, ...

ORDER BY queryId, swithPersonId, PK, Half

This way the results of the first query will always come before the results of the second query.

Edit - complete query

SQL

with cte as (
	select sp.PK as swithPersonId, sp.PTPK, emp.Employee_Name, emp.PK, pro.Name, sp.SwitchDate, sp.Half, sp.LeadBy, sp.EmployeeNamePK
	from switch_person sp
	INNER JOIN projects pro	ON sp.PTPK = pro.PK
	INNER JOIN employee emp	ON sp.EmployeeNamePK = emp.PK
	where pro.TeamLead = 33
		AND sp.SwitchDate = '2015-6-25'
		AND sp.Half IN (1,2)
)
, cte2 as
(
	select 1 as queryId, swithPersonId, PTPK, Employee_Name, PK, Name, SwitchDate, Half
	from cte where LeadBy <> EmployeeNamePK

	union all

	select 2 as queryId, swithPersonId, PTPK, Employee_Name, PK, Name, SwitchDate, Half
	from cte where LeadBy = EmployeeNamePK
)
select swithPersonId, PTPK, Employee_Name, PK, Name, SwitchDate, Half
from cte2
order by queryId, swithPersonId, PK, Half

First CTE gets the data you need. Second CTE separates the two queries based on LeadBy value where all records with "LeadBy <> EmployeeNamePK" will get before any records where "LeadBy = EmployeeNamePK". Finally we only select those columns which we are interested in and order the results.

Sreekanth Mothukuru · Answer 3 · 2015-06-26T02:05:00

The query should be something like this:

Select swithPersonId, PTPK, Employee_Name, PK, Name, SwitchDate, Half
from

(
SELECT sp.PK as swithPersonId, sp.PTPK, emp.Employee_Name, emp.PK, pro.Name, sp.SwitchDate, sp.Half, 1 as filter
from switch_person sp
            INNER JOIN projects pro
            ON sp.PTPK = pro.PK
            INNER JOIN employee emp
            ON sp.EmployeeNamePK = emp.PK
            WHERE pro.TeamLead = 33
            AND sp.SwitchDate = '2015-6-25'
            AND sp.Half IN (1,2)
            AND sp.LeadBy <> sp.EmployeeNamePK
            ORDER BY pro.PK,emp.PK, sp.Half

union all

SELECT sp.PK as swithPersonId, sp.PTPK, emp.Employee_Name, emp.PK, pro.Name, sp.SwitchDate, sp.Half, 2 as filter
from switch_person sp
            INNER JOIN projects pro
            ON sp.PTPK = pro.PK
            INNER JOIN employee emp
            ON sp.EmployeeNamePK = emp.PK
            WHERE sp.SwitchDate = '2015-6-25'
            AND sp.Half IN (1,2)
            AND sp.LeadBy = sp.EmployeeNamePK
            ORDER BY pro.PK,emp.PK, sp.Half
)

order by filter

maintain order of two queries after union

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0