Order by Clause in Union

Question

Order by Clause in Union

Newbi

SSCertifiable

Points: 6546
More actions
July 4, 2017 at 9:39 pm

#104651

My ORDER BY clause after joining data using UNION is not giving me correct result. Anyone has any idea why?
SELECT Mnth,ProductID,Project_Nofrom(SELECT left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2) as Korder by Mnth;
create table T2(ID int,Project_No int,OrderDate date )insert into T2 values (2569,13,'2012-01-13')insert into T2 values (2843,14,'2012-01-30')insert into T2 values (2888,15,'2012-02-01')insert into T2 values (2889,15,'2012-03-01')insert into T2 values (2890,15,'2012-04-01')insert into T2 values (2891,18,'2012-02-01')insert into T2 values (2892,30,'2012-03-01')insert into T2 values (2893,18,'2012-06-01')insert into T2 values (2894,11,'2012-04-01')insert into T2 values (2895,11,'2012-05-01')insert into T2 values (2896,15,'2012-11-01')insert into T2 values (2897,14,'2012-12-01')insert into T2 values (2898,12,'2012-08-01')insert into T2 values (2899,20,'2012-09-01')insert into T2 values (2900,15,'2012-10-01')insert into T2 values (2901,19,'2012-10-01')insert into T2 values (2901,19,'2012-07-19')

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24448 More actions · Answer 1

Firstly, What results are you expecting?

Secondly, your select statement is merely a union of 12 identical select statements
SELECT Mnth, ProductID, Project_No FROM ( SELECT LEFT(DATENAME(MONTH,OrderDate),3) AS Mnth, ID AS ProductID, Project_No, OrderDate FROM T2 UNION SELECT LEFT(DATENAME(MONTH,OrderDate),3) AS Mnth, ID AS ProductID, Project_No, OrderDate FROM T2 UNION SELECT LEFT(DATENAME(MONTH,OrderDate),3) AS Mnth, ID AS ProductID, Project_No, OrderDate FROM T2 UNION SELECT LEFT(DATENAME(MONTH,OrderDate),3) AS Mnth, ID AS ProductID, Project_No, OrderDate FROM T2 UNION SELECT LEFT(DATENAME(MONTH,OrderDate),3) AS Mnth, ID AS ProductID, Project_No, OrderDate FROM T2 UNION SELECT LEFT(DATENAME(MONTH,OrderDate),3) AS Mnth, ID AS ProductID, Project_No, OrderDate FROM T2 UNION SELECT LEFT(DATENAME(MONTH,OrderDate),3) AS Mnth, ID AS ProductID, Project_No, OrderDate FROM T2 UNION SELECT LEFT(DATENAME(MONTH,OrderDate),3) AS Mnth, ID AS ProductID, Project_No, OrderDate FROM T2 UNION SELECT LEFT(DATENAME(MONTH,OrderDate),3) AS Mnth, ID AS ProductID, Project_No, OrderDate FROM T2 UNION SELECT LEFT(DATENAME(MONTH,OrderDate),3) AS Mnth, ID AS ProductID, Project_No, OrderDate FROM T2 UNION SELECT LEFT(DATENAME(MONTH,OrderDate),3) AS Mnth, ID AS ProductID, Project_No, OrderDate FROM T2 UNION SELECT LEFT(DATENAME(MONTH,OrderDate),3) AS Mnth, ID AS ProductID, Project_No, OrderDate FROM T2 ) AS K ORDER BY Mnth;

The following SQL will generate exactly the same output
SELECT DISTINCT LEFT(DATENAME(MONTH,OrderDate),3) AS Mnth , ID AS ProductID , Project_No FROM T2 ORDER BY LEFT(DATENAME(MONTH,OrderDate),3);

How to post data/code on a forum to get the best help.

DesNorton SSC-Insane Points: 24448 More actions · Answer 2

Now, if your intention is to order by the month, then the following SQL may be what you are looking for
SELECT K.Mnth , K.ProductID , K.Project_No FROM ( SELECT DISTINCT LEFT(DATENAME(MONTH,OrderDate),3) AS Mnth , ID AS ProductID , Project_No , MonthNo = MONTH(OrderDate) FROM T2 ) AS K ORDER BY K.MonthNo;

How to post data/code on a forum to get the best help.

Newbi SSCertifiable Points: 6546 More actions · Answer 3

Newbi

SSCertifiable

Points: 6546

July 5, 2017 at 12:10 am

#1949397

I am trying to get like following:

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Newbi - Tuesday, July 4, 2017 9:39 PM
My ORDER BY clause after joining data using UNION is not giving me correct result. Anyone has any idea why?
SELECT Mnth,ProductID,Project_Nofrom(SELECT left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2UNION select left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDatefrom T2) as Korder by Mnth;
create table T2(ID int,Project_No int,OrderDate date )insert into T2 values (2569,13,'2012-01-13')insert into T2 values (2843,14,'2012-01-30')insert into T2 values (2888,15,'2012-02-01')insert into T2 values (2889,15,'2012-03-01')insert into T2 values (2890,15,'2012-04-01')insert into T2 values (2891,18,'2012-02-01')insert into T2 values (2892,30,'2012-03-01')insert into T2 values (2893,18,'2012-06-01')insert into T2 values (2894,11,'2012-04-01')insert into T2 values (2895,11,'2012-05-01')insert into T2 values (2896,15,'2012-11-01')insert into T2 values (2897,14,'2012-12-01')insert into T2 values (2898,12,'2012-08-01')insert into T2 values (2899,20,'2012-09-01')insert into T2 values (2900,15,'2012-10-01')insert into T2 values (2901,19,'2012-10-01')insert into T2 values (2901,19,'2012-07-19')

You're getting exactly the result you're requesting.
In your query you're ordering by [Mnth], which is 3 character long representation of months names.
In you output they are ordered correctly, according to the collation rules applied on your database.

If you want to order by month number rather than name, then you need to to exactly that - order by month number.
But if your query will happen to go beyond a single calendar year that 2 Januaries from different year will go together.

So, I'd suggest to sort by the date value of the month, and convert to names only on the latest stage:
SELECT left(datename(month,OrderMonth),3)As Mnth, ProductID, Project_No, OrderDate from( SELECT dateadd(mm, datediff(mm, 0, OrderDate),0) As OrderMonth, ID as ProductID, Project_No, OrderDate from T2 ) as K order by OrderMonth;

_____________
Code for TallyGenerator

Newbi SSCertifiable Points: 6546 More actions · Answer 5

I see... I didn't it was sorted alphabetically in my query.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Why are we overcomplicating this?
SELECT left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDate FROM T2 order by month(OrderDate);
Is there a reason to use all those UNION?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

GaryV Hall of Fame Points: 3307 More actions · Answer 7

Luis Cazares - Thursday, July 6, 2017 6:04 AM
Why are we overcomplicating this?
SELECT left(datename(month,OrderDate),3)As Mnth, ID as ProductID,Project_No,OrderDate FROM T2 order by month(OrderDate);
Is there a reason to use all those UNION?

Perhaps he's obfuscating the true name of 12 different tables.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

gvoshol 73146 - Friday, July 7, 2017 5:25 AM
Perhaps he's obfuscating the true name of 12 different tables.

That's why I'm asking instead of assuming something.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2