SQL: Using Top 1 in UNION query with Order By
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?
4 Solutions collect form web for “SQL: Using Top 1 in UNION query with Order By”
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'
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).
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.
I believe the above queries are excluding 05/01/2009 by using < and > instead of <= and >=.