SQL: использование Top 1 в запросе UNION с помощью Order By

У меня есть таблица ниже

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

Я должен найти все ставки, действующие для текущей даты и после нее. Чтобы получить текущую эффективную ставку, я использую

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

для ставок после текущей даты запрос

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

Чтобы объединить эти два результата, я использую объединение как

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'

Ожидаемый результат

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

Но я получаю фактический результат как

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

У меня нет подсказки, почему это происходит? Любые предложения?

Ответы

Ответ 1

Он работает следующим образом:

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

Заказ By в выражении select, который является частью объединения, игнорируется. Следовательно, ваш TOP 1 выбирает какую-то суровую запись (вероятно, первая запись кластеризованным ключом для таблицы).

Ответ 3

Order By недействителен при использовании с Union...

Я обработал быстрый и грязный материал, используя Common Table Expression, с некоторыми трюками из ранга и дела, чтобы получить результаты, которые вы искали.

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

Чтобы объяснить, что происходит...

CTE определяет флаги скорости, даты, текущего и сортировки, возвращаемые из запроса...

CASE разделяет результаты на те, которые были до даты поиска, и те, которые находятся после даты поиска. Мы используем результаты из случая (Cur) в нашем объединении, чтобы вывести результаты из списка секционированных..

Затем функция Rank() сортирует список, создавая раздел по тем же критериям, что и оператор CASE, чтобы отделить список. Затем мы заказываем по дате вступления в силу по убыванию. Это займет "прошлый" список и сделает его самым текущим "прошлым" рангом записи 1..

Затем в объединенной части запроса..

В верхней части мы получаем ранги и даты из "прошлого" списка (cur = 0) и первой записи в "прошлом" списке.. (sort = 1).., который вернет 1 запись (или 0, если нет записей, которые были до даты поиска).

Затем мы объединяем это со всей записью из "текущего" списка (cur = 1)

Тогда, наконец, возьмем РЕЗУЛЬТАТЫ UNION.. и закажем, чтобы по дате вступления давали нам все текущие записи и "самую последнюю" предыдущую запись.

Ответ 4

Я считаю, что вышеупомянутые запросы исключают 05/01/2009, используя < и > вместо <= и > =.