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?

Answer 1

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

Answer 2

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'

Answer 3

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).

Answer 4

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.