I had a college exercise where I had to demonstrate a handy use of Union. I am using a sample TSQLV4 database.

I came up with the following code:

FROM

(SELECT TOP 5 
cust.companyname as [Customer], 
SUM(ord.total) AS [Total Spent],
COUNT(ord.orderid) AS [Total Orders],
'Top Spender' AS [Description]

FROM sales.Orders ord 
JOIN sales.customers cust ON cust.custid = ord.custid
GROUP BY [companyname]
ORDER BY 2 desc) base

UNION

SELECT [Customer],[Total Spent],[Total Orders],[Description]
FROM

(SELECT TOP 5 
cust.companyname as [Customer], 
SUM(ord.total) AS [Total Spent],
COUNT(ord.orderid) AS [Total Orders],
'Lowest Spender' AS [Description]

FROM sales.Orders ord 
JOIN sales.customers cust ON cust.custid = ord.custid
GROUP BY [companyname]
ORDER BY 2 ASC) base

UNION

SELECT [Customer],[Total Spent],[Total Orders],[Description]
FROM

(SELECT TOP 5 
cust.companyname as [Customer], 
SUM(ord.total) AS [Total Spent],
COUNT(ord.orderid) AS [Total Orders],
'Most Orders' AS [Description]

FROM sales.Orders ord 
JOIN sales.customers cust ON cust.custid = ord.custid
GROUP BY [companyname]
ORDER BY 3 desc) base

UNION

SELECT [Customer],[Total Spent],[Total Orders],[Description]
FROM

(SELECT TOP 5 
cust.companyname as [Customer], 
SUM(ord.total) AS [Total Spent],
COUNT(ord.orderid) AS [Total Orders],
'Least Orders' AS [Description]

FROM sales.Orders ord 
JOIN sales.customers cust ON cust.custid = ord.custid
GROUP BY [companyname]
ORDER BY 3 asc) base
order by 4,2

It returns:

It works fine for the purpose of the drill (and the homework got an A ), but I notice there is an issue with the sorting and can't seem to let it go, lol.

I chose to order the whole "UNIONED" query (please correct with the right nomenclature, if any) by the 4th column so the rows are stuck together by category, but then I had to chose between the second ORDER BY level to be the 2nd or 3rd columns (Total Spent, Total Orders). The problem is that if I sort by one or another, the second level sorting won't make sense for half of the categories (Least/Most orders sorted by total spent, or Lowest/Top Spenders sorted by total orders).

I have already created a table function that picks up a varchar (planning it to be the description column value) and returning the whole UNIONED query custom ordered, depending on IF statements that read the value for the @description parameter.

Now I cannot seem to create that last line that I thought would give me my intended behavior.

[...]
ORDER BY 3 asc) base
ORDER BY dbo.FtOrdenamiento(base.[Description])

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.FtOrdenamiento", or the name is ambiguous.

Could anyone suggest a way to achieve this?

CodePudding user response：

put all of your query in a cte and select your fields in the cto after that write your order by

for e.g:

WITH q
AS
(
SELECT p.ProductID,p.Name,p.ModifiedDate
FROM Production.Product p

UNION 

SELECT ProductCategoryID,Name,ModifiedDate
FROM Production.ProductCategory
)
SELECT *
FROM q
ORDER BY q.Name,q.ModifiedDate

CodePudding user response：

You can add a ROW_NUMBER in each of your subqueries and use this to order the final result:

e.g.

SELECT s.Client , s.InvoiceAmount,s.Description,s.RN
FROM    (
    SELECT TOP 5 f.Client , f.InvoiceAmount,'Small clients' as Description,ROW_NUMBER() OVER (ORDER BY f.InvoiceAmount) RN
    FROM dbo.FactSales f
    ORDER BY f.InvoiceAmount 
    ) s

UNION ALL

SELECT l.Client , l.InvoiceAmount,Description,l.RN
FROM    (
    SELECT TOP 5 f.Client , f.InvoiceAmount,'Large clients' as Description,ROW_NUMBER() OVER (ORDER BY f.InvoiceAmount DESC) RN
    FROM dbo.FactSales f
    ORDER BY f.InvoiceAmount DESC
    ) l

ORDER BY Description,RN;