CODEWITHSUNDEEP
sqlsql-servert-sqlunion
Eric Bergman
Eric Bergman

Reputation: 1443

TSQL - Order Query within a UNION

Let's say I have the following Tables

Table1

Name        | Date Created

John Doe    | 09-10-2016
Mike Smith  | 08-05-2016
Super Man   | 09-07-2016

Table2

Name          Date_Created
Super Mario | 02-01-2016
James Bond  | 05-01-2016

Table3 

Name          Date_Created
Mega Man    | 12-25-2016
Batman Jr   | 12-05-2016

If I want to join all these tables together I'd use a UNION

SELECT * FROM Table1
UNION
SELECT * FROM Table2
UNION
SELECT * FORM Table3

This produces:

Name          Date_Created
John Doe    | 09-10-2016
Mike Smith  | 08-05-2016
Super Man   | 09-07-2016
Super Mario | 02-01-2016
James Bond  | 05-01-2016
Mega Man    | 12-25-2016
Batman Jr   | 12-05-2016

Is there a way to order the query in Table2 Only by 'Date Created' DESC such that the results are displayed like this:

Name          Date_Created
John Doe    | 09-10-2016
Mike Smith  | 08-05-2016
Super Man   | 09-07-2016

James Bond  | 05-01-2016       <-- DESC May 01 2016 then Feb 01 2016
Super Mario | 02-01-2016

Mega Man    | 12-25-2016
Batman Jr   | 12-05-2016

Upvotes: 0

Views: 57

Answers (2)

sree
sree

Reputation: 1960

Below is my answer for the same

CREATE TABLE #Table1(Name varchar(20),DateCreated date)
CREATE TABLE #Table2(Name varchar(20),DateCreated date)
CREATE TABLE #Table3(Name varchar(20),DateCreated date)

INSERT INTO #Table1 VALUES('John Doe','09-10-2016'),
('Mike Smith','08-05-2016'),
('Super Man','09-07-2016')

INSERT INTO #Table2 VALUES ('Super Mario','02-01-2016'),
('James Bond','05-01-2016')

INSERT INTO #Table3 VALUES ('Mega Man','12-25-2016'),
('Batman Jr','12-05-2016')


--Using CTE
With cte(Name,DateCreated) as
(
SELECT TOP (SELECT COUNT(*) FROM #Table2) * FROM #Table2 ORDER BY DateCreated DESC
)
SELECT * FROM #Table1 UNION ALL
SELECT * FROM cte UNION ALL
SELECT * FROM #Table3

GO

--Using Subquery
SELECT * FROM #Table1 UNION ALL
SELECT  FROM (SELECT TOP (SELECT COUNT(*) FROM #Table2)  FROM #Table2 ORDER BY DateCreated DESC)a UNION ALL
SELECT * FROM #Table3

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269673

If I want to join all these tables together I'd use a UNION

Wrong. If you want to combine them all, your default should be UNION ALL. Only use UNION when you want to incur the overhead of removing duplicates.

Second misconception: You cannot order part of a result set. A result set (like a table) represents an unordered set, unless you explicitly include an ORDER BY. So, you can order the whole thing but not a part of it.

You can bring the results together and do funky ordering things:

SELECT t.*
FROM ((SELECT t1.*, 1 as which FROM Table1 t1
      ) UNION ALL
      (SELECT t2.*, 2 as which FROM Table2 t2
      ) UNION ALL
      (SELECT t3.*, 3 as which FROM Table3 t3
      )
     ) t
ORDER BY which,
         (CASE WHEN which = 1 THEN name END),
         (CASE WHEN which = 2 THEN date_created END) DESC,
         (CASE WHEN which = 3 THEN name END) DESC;

Upvotes: 2

Related Questions