I have a table as below.
id date value
1 2011-10-01 xx
1 2011-10-02 xx
...
1000000 2011-10-01 xx
Then I have 1000 ids each associates with a date. I would like to perform something as below:
SELECT id, date, value
FROM the table
WHERE (id, date) IN ((id1, <= date1), (id2, <= date2), (id1000, <= date1000))
What's the best way to achieve above query?
You didn't specify your DBMS, so this is standard SQL.
You could do something like this:
with list_of_dates (id, dt) as (
values
(1, date '2016-01-01'),
(2, date '2016-01-02'),
(3, date '2016-01-03')
)
select
from the_table t
join list_of_dates ld on t.id = ld.id and t.the_date <= ld.dt;
This assumes that you do not have duplicates in the list of dates.
Update - now that the DBMS has been disclosed.
For SQL Server you need to change that to:
with list_of_dates (id, dt) as (
values
select 1, cast('20160101' as datetime) union all
select 2, cast('20160102' as datetime) union all
select 3, cast('20160103' as datetime)
)
select
from the_table t
join list_of_dates ld on t.id = ld.id and t.the_date <= ld.dt;
since this is info known ahead of time build a temp table of this info and then join to it
create table #test(id int, myDate date)
insert into #test(id,myDate) values
(1, '10/1/2016'),
(2, '10/2/2016'),
(3, '10/3/2016')
select a.id, a.date, a.value
from table as a
inner join
#test as b on a.id=b.id and a.date<=b.myDate
where (id, date) IN ( (1, date '2016-01-01'), (2, '2016-01-02'), ...)What exactly is that<=supposed to do?