Order individual query results in UNION ALL query

Question

Hi all,

I am using SQL Server 2000 (v8) and need to generate a query that will return results from different queries where each result set is sorted. Below is an illustrative example of what I am looking for.

Scripts to create table and data are at the end.

A table contains sales information. I need the Product Id and the amount of sale for that item. That is, I need the query to bring back two columns. The table contains sales transactions in three stages of completion:

0: Sale just made, waiting for approval.
1: Sale has been approved
2: Sale is complete

I am interested only in stages 0 and 1.

The report must contain sales information for a given sales rep and be divided into two sections. The first for new sales (status = 0) and a second for approved sales (Status = 1). A total amount must follow each section. The detail must be ordered by product id.

Here is the catch: The reporting mechanism accepts a data set with two result sets. The first result set contains the header information and the second one contains the report's body. Up to now, this has worked fine because all the reports I've done have only one section, so I have been able to create one query for these.

So far, I have the following query:

select ProdId,Amount from Sales
where
PerId = 12 and
Status = 0

union all

select 0,SUM(Amount) from Sales where
PerId = 12 and
Status = 0

union all

select ProdId,Amount from Sales
where
PerId = 12 and
Status = 1

union all

select 0, SUM(Amount) from Sales where
PerId = 12 and
Status = 1

For testing, I have hard coded the sales rep id (12), but the final script needs to accept this as a parameter. Since I plan to put this script inside a stored procedure, I don't see a problem with it.

The above example works, but does not sort by product id. I added the sort with an ORDER BY statement at the end of the query, but the sort affects the results as a whole, and not by section as I need it. Adding ORDER BY at the end of each query is not allowed.

I also tried creating views for the two detail queries, adding an ORDER BY to the view, but that doesn't work either for reasons that I discovered after a brief investigation. I also tried sorting by Status and ProdId, but the UNION does not like it if this column is not included in the SELECT statement, something that I don't want.

I also found another possible solution by generating a temp table on the fly instead of explicitly specifying a table in the FROM clause, but alas, the SQL Server that is in use does not support that.

Given what is mentioned here, what options do I have? Any orientation and/or feedback is welcomed and appreciated. Thank you, Saga

Scripts follow

CREATE TABLE Sales (
    Id int IDENTITY(1,1),
    PerId int,
    ProdId int,
    SaleDate smalldatetime,
    Amount money,
    Status smallint
    );

insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (7,5,'2019-03-19',15700.00,0)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (11,9,'2019-03-13',15700.00,0)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (13,11,'2019-03-15',15700.00,0)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (12,6,'2018-06-17',18500.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (7,3,'2018-06-04',18500.00,0)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (8,9,'2019-08-08',18566.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (13,8,'2019-08-05',18566.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (9,4,'2020-01-20',18925.00,0)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (5,6,'2020-01-22',18925.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (5,6,'2019-05-09',22300.00,0)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (9,8,'2019-10-07',22300.00,0)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (8,5,'2019-05-08',22300.00,0)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (13,10,'2019-05-05',22300.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (11,12,'2019-10-03',22300.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (12,12,'2019-05-04',22300.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (12,13,'2019-10-04',22300.00,0)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (13,9,'2018-08-01',24000.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (10,6,'2018-08-02',24000.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (8,4,'2018-08-04',24000.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (5,3,'2018-03-09',25499.00,0)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (11,5,'2018-03-03',25499.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (13,4,'2018-03-05',25499.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (12,2,'2018-03-04',25499.00,0)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (13,4,'2019-05-10',27900.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (10,7,'2019-05-11',27900.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (9,5,'2019-05-12',27900.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (8,4,'2020-02-25',28915.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (13,8,'2020-02-22',28915.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (12,9,'2019-03-03',32299.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (9,2,'2019-03-06',32299.00,0)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (10,4,'2019-03-05',32299.00,0)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (5,7,'2018-11-21',36000.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (7,8,'2020-02-13',55622.00,0)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (12,8,'2020-02-08',55622.00,1)
insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (13,2,'2020-02-09',55622.00,0)   

Answer 1

Try this:

DECLARE @PerId int = 12;

SELECT ProdId, Amount
FROM (
    SELECT ProdId, Amount, 0 AS [Status], 0 AS SortNumber 
    FROM [dbo].[Sales] 
    WHERE [Status] = 0 AND PerId = @PerId
    UNION ALL
    SELECT 0 AS ProdId, SUM(Amount), 0 AS [Status], 1 AS SortNumber 
    FROM [dbo].[Sales] 
    WHERE [Status] = 0 AND PerId = @PerId
    UNION ALL
    SELECT ProdId, Amount, 1 AS [Status], 0 AS SortNumber 
    FROM [dbo].[Sales] 
    WHERE [Status] = 1 AND PerId = @PerId
    UNION ALL
    SELECT 0 AS ProdId, SUM(Amount), 1 AS [Status], 1 AS SortNumber  
    FROM [dbo].[Sales] 
    WHERE [Status] = 1 AND PerId = @PerId
) AS temp
ORDER BY [Status], SortNumber, ProdId;

Answer 2

I am using SQL Server 2000 (v8)

I hope you know that version 2000 is out-of-support since more then a very long time?

from different queries where each result set is sorted

ORDER BY is a post opperation and apply the the complete resultset.

Answer 3

Hi @Saga ，

In statements containing UNION ALL, ORDER BY is only allowed to appear after the last statement, and not in the middle SELECT statement. In addition, SQL Server does not allow the definition of ORDER BY clauses in the view. In these cases, we can use cte.
Please also check:

    DECLARE @PerId int = 12;  
      
    ;WITH cte  
    AS(SELECT ProdId,Amount,[Status] FROM Sales  
    WHERE PerId = @PerId and [Status] = 0  
    UNION ALL  
    SELECT 0,SUM(Amount),0 as [Status] FROM Sales   
    WHERE PerId = @PerId and [Status] = 0  
    UNION ALL  
    SELECT ProdId,Amount,[Status] FROM Sales  
    WHERE PerId =@PerId and [Status] = 1  
    UNION ALL  
    SELECT 0, SUM(Amount),1 as [Status] FROM Sales   
    WHERE PerId =@PerId and [Status] = 1)  
      
    SELECT ProdId, Amount FROM cte  
    ORDER BY [Status],  
    CASE WHEN ProdId=0 THEN (SELECT MAX(ProdId)+1 FROM Sales )END,ProdId  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.