Skip to main content
added 100 characters in body
Source Link
J.D.
  • 39.5k
  • 12
  • 60
  • 134
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
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
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
Source Link
J.D.
  • 39.5k
  • 12
  • 60
  • 134

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