У меня есть таблица 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
на этапе выбора, если они не включены в подзапрос?
Я хочу увидеть только CBAAU 4 1/2 12/09/25 249, так как это самая высокая версия для данного описания инструмента.
используйте CROSS APPLY вместо INNER JOIN, он идеально подходит для присоединения к подзапросам TOP 1
Вместо использования 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». Проверяем дальше
@PeterLucas Попробуй еще раз. У меня был немного неправильный синтаксис.
Спасибо @LukeStorms
Спасибо за репутацию. В качестве примечания. Вероятно, это за пределами вашего выбора. Но обычно в именах таблиц нет точек. Потому что точка используется для разделения servername.databasename.tablename. Чаще используется CamelCase или символ подчеркивания.
Ура, я возьму это на борт. Это мой опыт работы с Python; )
Вы ничего не делаете для удаления каких-либо не максимальных версий. Если вы можете использовать выражения общей таблицы, то это всего лишь еще один шаг, чтобы найти максимальную версию для описания инструмента:
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
Извините, исправил сейчас.
Привет, Ричард. Получение неправильного синтаксиса рядом с ключевым словом "FROM".
Ржу не могу! Работающий!! Спасибо Ричард
Каково ваше определение
duplicate row
? Он должен отличаться от моего, так как я не вижу повторяющихся строк в вашем списке. Я вижу, чтоInstrumentDescription
(CBAAU 4 1/2 12/09/25) появляется дважды, но другие столбцы в этой строке различаются между первым и вторым экземплярами.