0

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

Improve this question
7
  • Not sure I understand your question. Are you asking how to return the results in a different order?
    – J.D.
    Commented Mar 28 at 12:58
  • I just want the read-rate column to sort in desc order.
    – Caspersky12
    Commented Mar 28 at 13:46
  • @Caspersky12, have you tried adding ORDER BY ReadRate DESC to the end of the query?
    – Dan Guzman
    Commented Mar 28 at 14:17
  • @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.
    – Caspersky12
    Commented Mar 28 at 14:40
  • 2
    The final ORDER BY ReadRate DESC will order all of the concatenated UNION ALL results, not just the final query.
    – Dan Guzman
    Commented Mar 28 at 15:10

1 Answer 1

Reset to default
2

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
Improve this answer
2
  • 1
    thank you J.D and Dan and the rest for the help
    – Caspersky12
    Commented Mar 29 at 6:16
  • @Caspersky12 NP! Cheers!
    – J.D.
    Commented Mar 29 at 13:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.