0

I am running the following query again my postgresql-9.6.5 database. I want to receive the result preserving the order of the array ('dpmYLNG', 'upXGxTB', 'tNV4rd5', '8o6Zejg').

SELECT taxi.id as taxi_id
FROM taxi
LEFT OUTER JOIN public.vehicle v ON (v.id = taxi.vehicle_id)
LEFT OUTER JOIN public.vehicle_description vd ON (v.id = vd.vehicle_id)
WHERE
vd.special_need_vehicle != true
AND vd.type_ != 'mpv'
AND taxi.id IN ('dpmYLNG', 'upXGxTB', 'tNV4rd5', '8o6Zejg');

I found references questions: ORDER BY the IN value list, but I don't think ORDER BY POSITION work for string.

My second approach would be to use a function: WITH ORDINALITY, but I am not really familiar with function in postgresql. https://www.postgresql.org/docs/current/functions-srf.html

I have try using the ORDER BY POSITION keyword with not success.

SELECT taxi.id as taxi_id
FROM taxi
LEFT OUTER JOIN public.vehicle v ON (v.id = taxi.vehicle_id)
LEFT OUTER JOIN public.vehicle_description vd ON (v.id = vd.vehicle_id)
WHERE
vd.special_need_vehicle != true
AND vd.type_ != 'mpv'
AND taxi.id IN ('dpmYLNG', 'upXGxTB', 'tNV4rd5', '8o6Zejg')
ORDER BY POSITION(id::text IN "('tNV4rd5', '8o6Zejg')");

I recieve the following error message: 

SQL Error [42703]: ERROR: column "('tNV4rd5', '8o6Zejg')" does not exist Position: 372.

I want to receive the value: 'tNV4rd5', but I am receiving the value: '8o6Zejg'.

I should not be the first person having this problem, Thank for the help.

Sébastien Blais
  • 15
  • 5
  • Why not create a table with your `taxi_ids` and a `priority` column? Then `JOIN` with that and `ORDER BY taxi_id.priority`? In that way, your list is scalable as more IDs are added. – daShier Aug 29 '19 at 21:11
  • The condition `vd.special_need_vehicle != true` defeats the outer join, converting it to an inner join. – The Impaler Aug 30 '19 at 01:11

0 Answers0