Use ORDER BY in Derived table

Question

I have 7 metric names and all these metrics should ideally be updated every month but sometimes that doesn't happen. In that case I'm required to carry forward my previous month metric value, red threshold and yellow threshold. All the data comes from Excel.

The have created the following query on sql-server-

select 
    withnull.[Metric Name],
    ISNULL(withnull.[Metric Value], withnullx.[Metric Value]) MetricValue,
    ISNULL(withnull.[Red Threshold], withnullx.[Red Threshold]) Red,
    ISNULL(withnull.[Yellow Threshold], withnullx.[Yellow Threshold]) Yellow,
    withnull.[Date]
from 

    (
    SELECT [Metric Value], [Red Threshold], [Yellow Threshold], Mon.[Date], Mon.[Metric Name]
    FROM

        (
        SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
        FROM [QMS Scorecard].[dbo].['#1 Effectiveness (SPC)$']

        UNION ALL

        SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
        FROM [QMS Scorecard].[dbo].['#1 MRB Effectiveness (Non-Conf)$']

        UNION ALL

        SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
        FROM [QMS Scorecard].[dbo].['#1 Effectiveness(Problem Solvi)$']

        UNION ALL

        SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
        FROM [QMS Scorecard].[dbo].['Calibration Passing "As Found" $']

        UNION ALL

        SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
        FROM [QMS Scorecard].[dbo].['Change Control Malfunction Rate$']

        UNION ALL

        SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
        FROM [QMS Scorecard].[dbo].['MSA passing rate$']

        UNION ALL

        SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
        FROM [QMS Scorecard].[dbo].['Unknown Failures (FMEA & Ctrl)$']
        ) Data

        RIGHT JOIN

        (
        SELECT [Date], [Metric Name]
        FROM [Dates$]
        --ORDER BY [Metric Name], [Date]
        ) Mon

    ON datepart(mm, Data.[Last Updated Date]) = datepart(mm, Mon.[Date])
    AND
    datepart(yyyy, Data.[Last Updated Date]) = datepart(yyyy, Mon.[Date])
    AND
    Data.[Metric Name] = Mon.[Metric Name]

    --ORDER BY [Metric Name], [Date]
    ) withnull

    outer apply

    (SELECT [Metric Value], [Red Threshold], [Yellow Threshold]
    FROM (SELECT * from (SELECT *, row_number() OVER (PARTITION BY [Metric Name] ORDER BY [Date] DESC) rn from
        (
        SELECT [Metric Value], [Red Threshold], [Yellow Threshold], Mon.[Date], Mon.[Metric Name]
        FROM

            (
            SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
            FROM [QMS Scorecard].[dbo].['#1 Effectiveness (SPC)$']

            UNION ALL

            SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
            FROM [QMS Scorecard].[dbo].['#1 MRB Effectiveness (Non-Conf)$']

            UNION ALL

            SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
            FROM [QMS Scorecard].[dbo].['#1 Effectiveness(Problem Solvi)$']

            UNION ALL

            SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
            FROM [QMS Scorecard].[dbo].['Calibration Passing "As Found" $']

            UNION ALL

            SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
            FROM [QMS Scorecard].[dbo].['Change Control Malfunction Rate$']

            UNION ALL

            SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
            FROM [QMS Scorecard].[dbo].['MSA passing rate$']

            UNION ALL

            SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
            FROM [QMS Scorecard].[dbo].['Unknown Failures (FMEA & Ctrl)$']

            ) Data

            RIGHT JOIN

            (
            SELECT [Date], [Metric Name]
            FROM [Dates$]
            ) Mon

        ON datepart(mm, Data.[Last Updated Date]) = datepart(mm, Mon.[Date])
        AND
        datepart(yyyy, Data.[Last Updated Date]) = datepart(yyyy, Mon.[Date])
        AND
        Data.[Metric Name] = Mon.[Metric Name]

        ) b ) d
        WHERE rn = 1) c
    WHERE 
        c.[Date]<withnull.[Date] and 
        withnull.[Metric Value] is null and
        c.[Metric Value] is not null and 
        c.[Red Threshold] is not null and 
        c.[Yellow Threshold] is not null

    ORDER BY [Metric Name], [Date] DESC
    ) withnullx

I have joined worksheet for all the Metrics using UNION ALL, and to have all the months for each Metric Name I did a right join.

Now I have Null values (Metric Value, Red Threshold, Yellow Threshold) for months with no records for those specific metric names.

With Outer Apply, I'm replacing these NULLS with previous month values. This requires the ORDER BY in second last line of the above code.

There's another Order BY in above code. "SELECT * from (SELECT *, row_number() OVER (PARTITION BY [Metric Name] ORDER BY [Date] DESC) rn" This helps me get not just top for the complete data but top for each Metric Name.

I'm hoping if I could use this code, I will be able to replace NULL values with previous month values for each Metric Name. But I get error saying "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified." Thank you in advance.

Answer 1

First - the error tells you what the problem is, but it doesn't explain why. Your order by is trying to sort a query. But that query is a subquery, and is used to apply to another table. In that case, the order of the subquery is totally irrelevant. SQL does not care what the order of the query results are in order to do a join/apply, and the main queries order is set by it's own order clause. Hence the order clause will actually not do anything, even if SQL allowed you to do it and didn't error.

I think what you are trying to do is only apply the first row of the subquery? In that case, you need to make sure that the query only returns rows that you want to apply, or that you can specify which rows you want. ORDER BY won't do this. You have already done this inside the subquery in an earlier step by setting a rownmuber, and then later limiting the rownumber to 1 - this would do exactly what you want, I think.

This is what your subquery you apply currently does

Q1 - Outer query with lots of where stuff (Date < date, Red and yellow not null, etc
   Q2 - Query that only allows most recent row per [Metric Name]
      Q3 - Query that gets data and adds a rownumber by date

Q3 gets the data and sorts them by date for each Metric name. Q2 then only selects the most recent row for each [metric name], all others are thrown out. Q1 then applies a whole lot of where clauses. The problem is, if the where clause fails, then you have no other rows to look at, because you have already thrown them out in Q2. And the where clause will always fail, because the most recent row is never going to have a date less than the row you are matching against.

What you need to do is somehow move all the where clauses to before you do the bit where you return only one row. That way you are take only the most recent valid row, not just the most recent row. You need to move the where clause to the Q3 query, the one with the row_number. The null clauses will move easily, but the date one will have to be rethought.