SQL: Using Top 1 in UNION query with Order By

Question

I have a table as below

Rate Effective_Date
---- --------------
5.6  02/02/2009
5.8  05/01/2009
5.4  06/01/2009
5.8  12/01/2009
6.0  03/15/2009

I am supposed to find the all rates that are effective for current date and after it. So to get the current effective rate, i use

SELECT TOP 1 * from table 
where effective_date < '05/05/2009' 
order by effective date desc

for the rates after the current date the query is

SELECT * from table 
where effective_date > '05/05/2009'

To combine these two result i use a union as

SELECT TOP 1 * from table 
where effective_date < '05/05/2009' 
order by effective date desc

UNION

SELECT * from table 
where effective_date > '05/05/2009'

The expected result is

Rate Effective Date
---- --------------
5.8  05/01/2009
5.4  06/01/2009
5.8  12/01/2009
6.0  03/15/2009

But I get the actual result as

Rate Effective Date
---- --------------
5.6  02/02/2009
5.4  06/01/2009
5.8  12/01/2009
6.0  03/15/2009

I don't have a clue as to why this happens? Any suggestions?

Derwent

I'm not sure your "expected" results are actually the results that you should be expecting.. 3/15/2009 is the "second to last" "most current previous" record before your search date of '5/5/2009'.. you should only be getting 3 records in your expected result set. – Maffa

Answer 1

It works this way:

select *
from (
    select top 1 *
    from table
    where effective_date <= '05/05/2009'
    order by effective_date desc
) as current_rate

union all

select *
from table
where effective_date > '05/05/2009'

Traceytrachea

Does this address the issue of 'top x' not being respected within a Union query? (I assume itr does but have not played with this in MSSS) – Stormi

Yes, the edited version of your answer does work (wrapping the order by query in a non-ordered by query for the union). As with cats, there's a dozen different ways to skin 'em. I verified your query to return the same results as mine. Yours is great for quick and dirty "gimmie the data now" situations... I like the CTE method I described because once it's written, you can do anything you want with the data. (ie, Give me the second most previous rate [but not the most previous] and all "current" rates) – Maffa

I also prefer to use the derived table method when I need to use TOP and run into the "UNION ate my ORDER BY" problem. – Ornis

FWIW this partially worked for me running MS SQL 2019. I had to put both SELECTs into subqueries and then UNION the two. Either SQL 2019 is playing differently or its because both my queries were TOP(10)..ORDER BY queries. – Marcin

Answer 2

The Order By in a select statement that is part of a union is ignored. Hence your TOP 1 is selecting some arbitary record (likely the first record by the clustered key for the table).

Cusick

Exactly and well said. I have run into this exact problem before. – Ornis

Answer 3

Order By is invalid when used with a Union...

I worked up a quickie and dirty thingy using Common Table Expression with some Rank and Case statement trickery to get the results you were looking for..

WITH CTE_RATES ( RATE, EFFECTIVE_DATE, CUR, SORT )
AS (
    SELECT 
        Rate,
        Effective_date,
        CASE WHEN Effective_date > '5/5/2009' THEN 1
             ELSE 0
        END,
        RANK() OVER (PARTITION BY
                         CASE WHEN EFFECTIVE_DATE > '5/5/2009' THEN 1
                              ELSE 0
                         END
                     ORDER BY EFFECTIVE_DATE DESC)
    FROM TestTable
)

SELECT RATE, EFFECTIVE_DATE
FROM (
    SELECT RATE, EFFECTIVE_DATE 
    FROM CTE_RATES 
    WHERE CUR = 0 AND SORT = 1

    UNION ALL

    SELECT RATE, EFFECTIVE_DATE
    FROM CTE_RATES
    WHERE CUR = 1
    ) AS QRY
ORDER BY EFFECTIVE_DATE

To explain what is happening...

The CTE defines the rate, date, current and sorting flags returned from the query...

The CASE separates the results into those that are prior to the search date, and those that are after the search date.. We use the results from the case (Cur) in our union to pull the results from the partitioned list..

The Rank() function then sorts the list by creating a partition on the same criteria that the CASE statement uses to separate the list.. then we order by the effective date in descending fashion. This will take the "past" list and make it's most current "past" entry rank 1..

Then in the union portion of the query..

In the top part, we're getting the rank and date from the "past" list (cur = 0) and the first entry in the "past" list.. (sort = 1).. that will return 1 record (or 0 if there are no records that are prior to the search date)..

Then we union that with all of the record from the "current" list (cur = 1)

Then finally.. we take the RESULTS of the UNION.. and order that by the effective date giving us all of the current records, and the "most current" previous record.

Maffa

Answer 4

I believe the above queries are excluding 05/01/2009 by using < and > instead of <= and >=.

Kyser