Monday, April 3, 2017

PostgreSQL: order by a list

Source: ORDER BY the IN value list
See answer #15 from Erwin Brandstetter
select ''''||lpad(co_id::text, 12, '0')||''',', serial_id
from crm_db.ht_co_sim
join resource_db.ht_sim_info using (sim_id)
join unnest('{8933240100000334751,
8933240100000334750,
8933240100000334731,
8933240100000334749,
8933240100000334730,
8933240100000334748,
8933240100000334729,
8933240100000334728,
8933240100000334747,
8933240100000334727,
8933240100000334746,
8933240100000334726,
8933240100000334725,
8933240100000334745,
8933240100000334724,
8933240100000334744,
8933240100000334733,
8933240100000334723,
8933240100000334732,
8933240100000334722}'::bigint[]) with ordinality as t(serial_id, ord) using (serial_id)
where ht_co_sim.from_date <= localtimestamp(0) and localtimestamp(0) < ht_co_sim.to_date
and ht_sim_info.from_date <= localtimestamp(0) and localtimestamp(0) < ht_sim_info.to_date
order by t.ord