Hi All,
I’m trying re-write some queries from MSSQL to MongoDB, I kind of stuck on subquerying data from other table and returning top 1 from it in resultset. I created MSSQL demo to demonstrate what I’d like to achieve.
declare @v_instr table
(
instr_id int,
instr_name varchar(100)
)
declare @v_stocks table
(
stock_instr_id int,
stock_type varchar(5),
stock_value numeric(20,5),
stock_ts datetime2(2)
)
insert into @v_stocks
values
(1,'REF',2.22,'2022-01-01'),
(1,'TRD',2.66,'2022-01-03'),
(1,'TRD',2.66,'2022-01-01'),
(1,'VOL',75765.0,'2022-01-01'),
(2,'TRD',6.66,'2022-01-01'),
(2,'TRD',8.66,'2022-01-04'),
(2,'VOL',465.66,'2022-01-04'),
(2,'REF',8.66,'2022-01-04')
insert into @v_instr
values
(1,'TestCompany1'),
(2,'TestCompany2')
select instr_id,
instr_name,
(select top 1 stock_value from @v_stocks
where stock_type='TRD'
and stock_instr_id = instr_id
order by stock_ts desc) as stock_TRD,
(select top 1 stock_value from @v_stocks
where stock_type='VOL'
and stock_instr_id = instr_id
order by stock_ts desc) as stock_VOL,
(select top 1 stock_value from @v_stocks
where stock_type='REF'
and stock_instr_id = instr_id
order by stock_ts desc) as stock_REF
from @v_instr
It boils down to select (select top 1 value from tb where tb.id = source.id and tb.col = 'param') from source
I’d appreciate any help.
Thanks!