muffin muffin - 1 year ago 45
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 Source

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 =;

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 (
      (1, 1),
      (5, 2),
      (1, 1)
select r.*
from public.record r
  join ids on =
order by ids.sort_order;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download