Выберите все записи из таблицы 1, но только строку в таблице 2 с максимальным номером версии

У меня есть таблица Trades.Transaction и таблица Trades.BondRef. Их можно соединить на InstrumentDescription, но при этом будет получаться от одной до многих строк, поскольку на каждый ISIN/CUSIP (транзакция) приходится несколько InstrumentDescription (BondRef). Я хотел бы присоединиться, но отображать только строку из Trades.BondRef, имеющую максимальный номер версии. Я просмотрел множество сообщений и придумал код ниже.

SELECT  tr.TradeDate,
        tr.InstrumentDescription, 
        B.maxVersion,
        B.IsLatest, 
        B.Isin,
        B.Cusip,
        B.RbcType1,
        B.RbcType2,
        B.RbcType3  
FROM [trade_management].[dbo].[Trades.Transaction] tr   WITH (NOLOCK)
INNER JOIN (
            SELECT  InstrumentDescription,
                    MAX(version) maxVersion,
                    IsLatest, 
                    Isin,
                    Cusip,
                    RbcType1,
                    RbcType2,
                    RbcType3  
            FROM [trade_management].[dbo].[Trades.BondRef] 
            WHERE ValidTo between '2018-10-30 00:00:00.0000000 +00:00' and '2018-10-30 23:59:29.0000000 +00:00' 
            GROUP BY InstrumentDescription,IsLatest, Isin,Cusip,RbcType1,RbcType2,RbcType3
            ) AS B
ON B.InstrumentDescription = tr.InstrumentDescription
WHERE
(tr.OrigSystem = 'RBCE TOMS' OR tr.OrigSystem = 'SALE')
and (BookingAccountType = 'CLIENT' OR BookingAccountType = 'MASTER') 
and tr.BookingAccountFacilitatorTeamCode in ('ESF','MJC','43B','DWV','G9J','698','9DN','A2T','AX3') -- HK Sales
and tr.IsLatest = 1 
and tr.Status not in ('Cancelled') 
and tr.TradeDate between '2018-10-30 00:00:00.0000000 +00:00' and '2018-10-30 23:59:29.0000000 +00:00'  
order  by tr.tradedate

Я получаю повторяющиеся строки, которые возвращаются, поскольку моя группа включает Isin и Cusip. Обратите внимание, что CBAAU 4 1/2 12/09/25 с Version 249 должна быть единственной возвращаемой строкой.

TradeDate   InstrumentDescription   maxVersion  Isin    Cusip   RbcType1    RbcType2    RbcType3
2018-10-30  NESNVX 3 1/8 03/22/23   124 XS1796233150    NULL        CORP    INDUSTRIAL  EURO_MTN
2018-10-30  HSBC 6 1/4 PERP         116 US404280BN80    404280BN8   CORP    BANK    GLOBAL
2018-10-30  CBAAU 4 1/2 12/09/25    248 US2027A0HR32    2027A0HR3   CORP    BANK    PRIV_PLACEMENT
2018-10-30  CBAAU 4 1/2 12/09/25    249 US2027A1HR15    2027A1HR1   CORP    BANK    EURO-DOLLAR
2018-10-30  EIB 8 3/4 08/18/25      434 XS1274823571    NULL       SUPRA    NATIONAL    EURO_MTN

Но если я их удалю, я могу отобразить поля.

Column 'trade_management.dbo.Trades.BondRef.Isin' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Итак, как я могу получить столбцы в Trades.BondRef на этапе выбора, если они не включены в подзапрос?

Каково ваше определение duplicate row? Он должен отличаться от моего, так как я не вижу повторяющихся строк в вашем списке. Я вижу, что InstrumentDescription (CBAAU 4 1/2 12/09/25) появляется дважды, но другие столбцы в этой строке различаются между первым и вторым экземплярами.

Richard Hansell 31.10.2018 11:20

Я хочу увидеть только CBAAU 4 1/2 12/09/25 249, так как это самая высокая версия для данного описания инструмента.

Peter Lucas 31.10.2018 11:22

используйте CROSS APPLY вместо INNER JOIN, он идеально подходит для присоединения к подзапросам TOP 1

Cato 31.10.2018 11:26
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
3
52
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Вместо использования GROUP BY и MAX вы можете использовать оконную функцию ROW_NUMBER.

Так как ROW_NUMBER может быть отдан заказ.
Чтобы определить, какая запись будет иметь row_number = 1.

И вы также можете комбинировать ORDER BY ROW_NUMBER с TOP 1 WITH TIES.

...
INNER JOIN (
        SELECT TOP 1 WITH TIES
                InstrumentDescription,
                version AS maxVersion,
                IsLatest, 
                Isin,
                Cusip,
                RbcType1,
                RbcType2,
                RbcType3  
        FROM [trade_management].[dbo].[Trades.BondRef] 
        WHERE ValidTo between '2018-10-30 00:00:00.0000000 +00:00' and '2018-10-30 23:59:29.0000000 +00:00' 
        ORDER BY ROW_NUMBER() OVER (PARTITION BY InstrumentDescription ORDER BY version DESC)
        ) AS B
ON B.InstrumentDescription = tr.InstrumentDescription
...

Получение неправильного синтаксиса рядом с ключевым словом «BY». Проверяем дальше

Peter Lucas 31.10.2018 11:40

@PeterLucas Попробуй еще раз. У меня был немного неправильный синтаксис.

LukStorms 31.10.2018 11:49

Спасибо @LukeStorms

Peter Lucas 31.10.2018 12:15

Спасибо за репутацию. В качестве примечания. Вероятно, это за пределами вашего выбора. Но обычно в именах таблиц нет точек. Потому что точка используется для разделения servername.databasename.tablename. Чаще используется CamelCase или символ подчеркивания.

LukStorms 31.10.2018 12:19

Ура, я возьму это на борт. Это мой опыт работы с Python; )

Peter Lucas 31.10.2018 12:26

Вы ничего не делаете для удаления каких-либо не максимальных версий. Если вы можете использовать выражения общей таблицы, то это всего лишь еще один шаг, чтобы найти максимальную версию для описания инструмента:

WITH B AS (            SELECT  InstrumentDescription,
                    MAX(version) maxVersion,
                    IsLatest, 
                    Isin,
                    Cusip,
                    RbcType1,
                    RbcType2,
                    RbcType3  
            FROM [trade_management].[dbo].[Trades.BondRef] 
            WHERE ValidTo between '2018-10-30 00:00:00.0000000 +00:00' and '2018-10-30 23:59:29.0000000 +00:00' 
            GROUP BY InstrumentDescription,IsLatest, Isin,Cusip,RbcType1,RbcType2,RbcType3),
C AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY InstrumentDescription ORDER BY maxVersion DESC) AS version_id FROM B)
SELECT  tr.TradeDate,
        tr.InstrumentDescription, 
        C.maxVersion,
        C.IsLatest, 
        C.Isin,
        C.Cusip,
        C.RbcType1,
        C.RbcType2,
        C.RbcType3  
FROM [trade_management].[dbo].[Trades.Transaction] tr   WITH (NOLOCK)
INNER JOIN C
ON C.InstrumentDescription = tr.InstrumentDescription AND c.version_id = 1
WHERE
(tr.OrigSystem = 'RBCE TOMS' OR tr.OrigSystem = 'SALE')
and (BookingAccountType = 'CLIENT' OR BookingAccountType = 'MASTER') 
and tr.BookingAccountFacilitatorTeamCode in ('ESF','MJC','43B','DWV','G9J','698','9DN','A2T','AX3') -- HK Sales
and tr.IsLatest = 1 
and tr.Status not in ('Cancelled') 
and tr.TradeDate between '2018-10-30 00:00:00.0000000 +00:00' and '2018-10-30 23:59:29.0000000 +00:00'  
order  by tr.tradedate

Извините, исправил сейчас.

Richard Hansell 31.10.2018 11:48

Привет, Ричард. Получение неправильного синтаксиса рядом с ключевым словом "FROM".

Peter Lucas 31.10.2018 11:53

Ржу не могу! Работающий!! Спасибо Ричард

Peter Lucas 31.10.2018 12:06

Другие вопросы по теме