muffin muffin - 7 months ago 7
SQL Question

Retrieve same number of records in an ID IN query even with a duplicate

I'm using PostgreSQL. If I have an id in query say:

select * from public.record where id in (1,5,1);


this will only give me two rows, because the id 1 has a duplicate. But what if I want to display a set of records containing :

id | value
1 | A
5 | B
1 | A


Regardless of the reason why I would do this, is this possible?

Answer

You can do that by joining the values:

with ids (id) as (
  values (1),(5),(1)
) 
select r.*
from public.record r
  join ids on r.id = ids.id;

If you need to keep the order of the parameter list, you need to add a column to sort on:

with ids (id, sort_order) as (
  values 
      (1, 1),
      (5, 2),
      (1, 1)
) 
select r.*
from public.record r
  join ids on r.id = ids.id
order by ids.sort_order;
Comments