CODEWITHSUNDEEP
sqlsql-serversubquerysql-order-by
Smithy
Smithy

Reputation: 2190

How to ORDER BY before UNION

I have a problem where I need to UNION 2 tables.

The first table instance is for the headers of the results:

SELECT 'Department', 'ItemNumber', 'ProductDescription', 'Category', 'Group', 'SubGroup', 'Total Qty', 'Total Sales','07:00', '07:30'

Then I UNION on another table and it all works fine:

    UNION ALL 
SELECT i.STRDEPTCODE, CAST(i.LINTITEMNUMBER AS nvarchar), i.STRPRODUCTDESCRIPTION, i.STRCATCODE, i.STRGROUPCODE, i.STRSUBGROUPCODE, 

ISNULL((SELECT CAST(SUM(d.DBLQTYSOLD) AS nvarchar) FROM DAILYSALES d  WHERE d.STRSALETYPE = 'I' AND d.LINTITEMNUMBER = i.LINTITEMNUMBER AND d.DTMSALEDATETIME >= '2015-02-26 07:00:00' AND d.DTMSALEDATETIME < '2015-02-26 08:00:00' ), 0) AS 'Total Qty',  
ISNULL((SELECT CAST(SUM(d.CURSALES) AS nvarchar) FROM DAILYSALES d  WHERE d.STRSALETYPE = 'I' AND d.LINTITEMNUMBER = i.LINTITEMNUMBER AND d.DTMSALEDATETIME >= '2015-02-26 07:30:00' AND d.DTMSALEDATETIME < '2015-02-26 09:00:00' ), 0) AS 'Total Sales',
ISNULL((SELECT CAST(SUM(d.DBLQTYSOLD) AS nvarchar) FROM DAILYSALES d  WHERE d.STRSALETYPE = 'I' AND d.LINTITEMNUMBER = i.LINTITEMNUMBER AND d.DTMSALEDATETIME >= '2015-02-26 06:30:00' AND d.DTMSALEDATETIME < '2015-02-26 07:00:00' ), 0) AS '07:00',
ISNULL((SELECT CAST(SUM(d.DBLQTYSOLD) AS nvarchar) FROM DAILYSALES d  WHERE d.STRSALETYPE = 'I' AND d.LINTITEMNUMBER = i.LINTITEMNUMBER AND d.DTMSALEDATETIME >= '2015-02-26 07:00:00' AND d.DTMSALEDATETIME < '2015-02-26 07:30:00' ), 0) AS '07:30'

FROM ITEM i LEFT OUTER JOIN  (

    SELECT i.LINTITEMNUMBER, SUM(ds.DBLQTYSOLD) AS QTY, SUM(ds.CURSELLPRICE1) AS Value  
    FROM ITEM i LEFT JOIN DAILYSALES ds ON i.LINTITEMNUMBER=ds.LINTITEMNUMBER 
    WHERE ds.STRSALETYPE = 'I' AND ds.DTMSALEDATETIME >= '2015-02-26 07:00:00' AND ds.DTMSALEDATETIME < '2015-02-26 08:00:00' 

    GROUP BY i.STRDEPTCODE, i.LINTITEMNUMBER, i.STRPRODUCTDESCRIPTION, i.STRCATCODE, i.STRGROUPCODE, i.STRSUBGROUPCODE  ) t  
    on t.LINTITEMNUMBER= i.LINTITEMNUMBER WHERE t.QTY > 0

The problem is I need to order by LINTITEMNUMBER (before it is parsed to a string) so it will order them correctly.

I have to include the headers in the results and not as column headers because the receiving program won't deal with the headers.

Upvotes: 1

Views: 2060

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Add a column that you can use to sort by where the first row has 0 or something that guarantees that it comes first.. Put the union query in a derived table and extract only the columns you need ordering by the introduced sorting column.

Your query somewhat simplified.

select T.STRDEPTCODE,
       T.ITEMNUMBER,
       T.STRPRODUCTDESCRIPTION
from (
     select 0 as SortCol,
            'Department' as STRDEPTCODE,
            'ItemNumber' as ITEMNUMBER,
            'ProductDescription' as STRPRODUCTDESCRIPTION
     union all
     select T.LINTITEMNUMBER,
            T.STRDEPTCODE,
            cast(T.LINTITEMNUMBER as varchar(11)),
            T.STRPRODUCTDESCRIPTION
     from YourTable as T
     ) as T
order by T.SortCol

Upvotes: 3

JustAPup
JustAPup

Reputation: 1780

Can't you just add ORDER BY at the end? Like this:

ORDER BY i.LINTITEMNUMBER

Or if this doesn't work, try converting it back to numeric

ORDER BY CAST(i.LINTITEMNUMBER AS NUMERIC)

Upvotes: 0

Related Questions