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

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'