SQL：在使用Order By的UNION查询中使用Top 1

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 个解决方案

#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'

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

将忽略作为联合一部分的select语句中的Order By。因此，您的TOP 1正在选择一些仲裁记录（可能是该表的聚簇键的第一条记录）。

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

我使用Common Table Expression和一些Rank和Case语句技巧来制作一个快速而又脏的东西，以获得您正在寻找的结果。

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

CTE定义查询返回的速率，日期，当前和排序标志......

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

CASE将结果分为搜索日期之前的结果和搜索日期之后的结果。我们使用联合中案例（Cur）的结果从分区列表中提取结果。

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

然后，Rank（）函数通过在CASE语句用于分隔列表的相同条件下创建分区来对列表进行排序。然后我们按降序方式按生效日期排序。这将采用“过去”列表并使其成为最新的“过去”条目等级1 ..

Then in the union portion of the query..

然后在查询的union部分..

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

在上面部分，我们从“过去”列表（cur = 0）和“过去”列表中的第一个条目获得排名和日期..（sort = 1）..将返回1条记录（或如果没有搜索日期之前的记录，则为0）

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

然后我们将其与“当前”列表中的所有记录联合起来（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.

最后......我们取得UNION的结果..并在生效日期之前命令我们提供所有当前记录，以及“最新”的前一记录。

#4

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

我相信上述查询排除05/01/2009，使用 <和> 而不是<=和> =。

#1