Display in asc order from multiple databases under multiple linked server and add two date parameters so that I can integrate it to my app

Question

I have a script below where it display a result from different databases under different linked Server. The query is just connected or linked thru union all and it seems there's no issue on its result. My only problem is I want to display the result in desc order by using the read-rate higher result regardless which linked server it came from. I have an idea if its possible to use the ff script to convert into subquery, but I don't know how I am able to use it with join also. See Script below:

SELECT TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
FROM [LINKED-SERVER1].[Database1].DBO.Table1 t1 
INNER JOIN
[LINKED-SERVER1].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName
UNION ALL
SELECT TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
FROM [LINKED-SERVER2].[Database1].DBO.Table1 t1 
INNER JOIN
[LINKED-SERVER2].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName
UNION ALL
SELECT TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
FROM [LINKED-SERVER3].[Database1].DBO.Table1 t1 
INNER JOIN
[LINKED-SERVER3].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName
UNION ALL
SELECT TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
FROM [LINKED-SERVER4].[Database1].DBO.Table1 t1 
INNER JOIN
[LINKED-SERVER4].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName

example RESULT:

enter image description here

• J.D.: Not sure I understand your question. Are you asking how to return the results in a different order?
• Caspersky12: I just want the read-rate column to sort in desc order.
• Dan Guzman: @Caspersky12, have you tried adding ORDER BY ReadRate DESC to the end of the query?
• Caspersky12: @DanGuzman I mean, order by as a whole since those query were getting results from different linked-server and different database, I can put order by on every select statement after group by, but I think the result could be on that particular select statement only.
• Dan Guzman: The final ORDER BY ReadRate DESC will order all of the concatenated UNION ALL results, not just the final query.

Answer 1

As Dan pointed out in the comments, when you add an ORDER BY clause to the last query in a UNION ALL (or UNION) clause, the engine is smart enough to know you want the entire results of that UNION ALL clause to be ordered that way.

So to accomplish your goal of ordering the results by the ReadRate column, all you need is a final ORDER BY clause like so:

SELECT TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
FROM [LINKED-SERVER1].[Database1].DBO.Table1 t1 
INNER JOIN
[LINKED-SERVER1].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName
UNION ALL
SELECT TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
FROM [LINKED-SERVER2].[Database1].DBO.Table1 t1 
INNER JOIN
[LINKED-SERVER2].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName
UNION ALL
SELECT TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
FROM [LINKED-SERVER3].[Database1].DBO.Table1 t1 
INNER JOIN
[LINKED-SERVER3].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName
UNION ALL
SELECT TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
FROM [LINKED-SERVER4].[Database1].DBO.Table1 t1 
INNER JOIN
[LINKED-SERVER4].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName
ORDER BY ReadRate DESC

Notice how the ORDER BY clause references an alias instead of column, showing that it means the entire result set, not just that specific query.

If you want to do extra coding to feel warm and fuzzy about it (aka make it more readable to future developers perhaps) then you can just wrap your query in parenthesis to make it a subquery, and then you can apply the ORDER BY clause to the outside of the subquery like so:

SELECT TrxnDTime, PlazaCode, PlazaName, Lane, Zone, TotalRead, TotalManual, TotalCount, ReadRate
FROM
(
 SELECT TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
 FROM [LINKED-SERVER1].[Database1].DBO.Table1 t1 
 INNER JOIN
 [LINKED-SERVER1].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
 WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
 GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName
 UNION ALL
 SELECT TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
 FROM [LINKED-SERVER2].[Database1].DBO.Table1 t1 
 INNER JOIN
 [LINKED-SERVER2].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
 WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
 GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName
 UNION ALL
 SELECT TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
 FROM [LINKED-SERVER3].[Database1].DBO.Table1 t1 
 INNER JOIN
 [LINKED-SERVER3].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
 WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
 GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName
 UNION ALL
 SELECT TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
 FROM [LINKED-SERVER4].[Database1].DBO.Table1 t1 
 INNER JOIN
 [LINKED-SERVER4].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
 WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
 GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName
) AS Results
ORDER BY ReadRate DESC

• J.D.: @Caspersky12 NP! Cheers!