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')
  
  
  

• DesNorton

  SSC-Insane

  Points: 24277

  More actions

  July 4, 2017 at 11:52 pm

  #1949394

  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: 24277

  More actions

  July 5, 2017 at 12:02 am

  #1949395

  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

  July 5, 2017 at 12:10 am

  #1949397

  I am trying to get like following: 
  

• Sergiy

  SSC Guru

  Points: 110206

  More actions

  July 5, 2017 at 12:59 am

  #1949402

  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

  July 5, 2017 at 8:01 pm

  #1949606

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

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  July 6, 2017 at 6:04 am

  #1949672

  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

  July 7, 2017 at 5:25 am

  #1949873

  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: 183698

  More actions

  July 7, 2017 at 5:32 am

  #1949875

  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