assume a table has id and date columns, is there a way to apply different date filter to each id?

one way I can think of is to add a flag column that is populated with an if ladder

if id=A and date>date1 then 1

elseif id=B and date>date2 then 1

elseif id=C and date>date3 then 1

else 0

and then select rows where flag=1

is there a better way?

CodePudding user response：

Simply use regular AND/OR constructions. E.g. something like:

where (id=A and date>date1)
   or (id=B and date>date2)
   or (id=C and date>date3)

CodePudding user response：

• you can build a category using case when
• later you can filter on the values like date_and_id_filter = 1 or date_and_id_filter in (1,2,3). Basically, you can play with it

with main as (
select *, 
case when id = 'a' and date>date1 then 1
     when id = 'b' and date>date2 then 1
     when id=  'c' and date>date3 then 1
else 0 end as date_and_id_filter
from <table_name>
)
select * from main where date_and_id_filter = 1