Union ORDER BY with expression

  • I have a union query that has two aliases vDay based upon :

    SELECT convert(varchar, datepart(dd, s.dtDate)) 'vDay' .... FROM Schedule as S

    UNION

    SELECT convert(varchar, datepart(dd, h.dtDate)) 'vDay' .... FROM Holidays as H

    However, I am trying to Order by vDay (but as an INT)... I tried ORDER BY CAST(vDay as INT) with no avail.  The ASP frontend needs vDay to be a varchar, but I need to order the recordset.


    "Life without progression is entropy"
    Sam Jaynes

  • Did you try

    SELECT  t.vDay FROM (SELECT CONVERT(VARCHARDATEPART(dds.dtDate)) 'vDay' .... FROM Schedule AS S

    UNION

    SELECT CONVERT(VARCHARDATEPART(ddh.dtDate)) 'vDay' .... FROM Holidays AS H

    ORDER BY vday

    Francis

  • Try This:

    Select tableName.Col1,tableName.Col2,tableName.Col3,... from (

     SELECT convert(varchar, datepart(dd, s.dtDate)) 'vDay' ....,datepart(dd, s.dtDate) as Dy FROM Schedule as S

     UNION

     SELECT convert(varchar, datepart(dd, h.dtDate)) 'vDay' ....,datepart(dd, H.dtDate) as Dy FROM Holidays as H) tableName

    Order by Dy

    Thanks

    Sreejith

  • Since the column is varchar, it would order things like 1,11,12,13,14,2,21,22,25,3,30,31


    "Life without progression is entropy"
    Sam Jaynes

  • I will try out the subquery T-SQL


    "Life without progression is entropy"
    Sam Jaynes

  • ok then how about

     

    SELECT  t.vDay ... FROM (SELECT CONVERT(VARCHARDATEPART(dds.dtDate)) 'vDay' ,CONVERT(intDATEPART(dds.dtDate)) 'vDayorderby' .... FROM Schedule AS S

    UNION

    SELECT CONVERT(VARCHARDATEPART(ddh.dtDate)) 'vDay' ,CONVERT(intDATEPART(dds.dtDate)) 'vDayorderby' .... FROM Holidays AS H

    ORDER BY vDayorderby 

    Francis

  • DATEPART returns int (see BOL)

    So, there is no point in using

    CONVERT(intDATEPART(dds.dtDate))

     

    _____________
    Code for TallyGenerator

  • You might try something like this from your original Query in lieu of the subquery to sort by:

    SELECT Case when DATEPART(dd,dtDate) < 10 then '0' + CONVERT(VARCHAR(1),DATEPART(dd, dtDate)) else Convert(Varchar(2),DATEPART(dd, dtDate))end as 'vDay' FROM Schedule  

     

    which will give you the Varchar(2) of the Day to sort by.

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

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