Querying an ordered list with OR or IN clause

Question

I am querying items from a database and display it to the user. (in my example I'll use contacts in table tContacts.

The user selects a couple of items in an order he decides (the user can random pick the items, so no column can be specified to order).

In the application I have the id's of those items in an ordered list.

Currently I query more details for those objects one-by-one.

Is it possible to create a query with an "ordered list", which returns the items in the same order?

something like (yes, this is ugly - probably also hitting the max sql query size):

SELECT * FROM (
    --how to append this list...
    SELECT 0 as listIndex, * from tContacts where conIdContact = 13259;
    SELECT 1 as listIndex, * from tContacts where conIdContact = 12472;
    [...]
    SELECT 568 as listIndex, * from tContacts where conIdContact = 12422;
)
ORDER BY listIndex

• OR: do i have to keep querying one-by-one
• OR: does this need a temp table (insert id and priority), then query, then drop temp table again.
• OR: query a batch and reorder the items in application code (this might be slow due possibly large lists)

note: a database independent solution is preferred (mariadb / mysql, postgresql and h2 are considered to be used in production.

my temp table concept:

CREATE TEMPORARY TABLE temp_OrderedContacts(
    listIndex INT NOT NULL DEFAULT 0,
    contactId INT NOT NULL DEFAULT 0
);

INSERT INTO temp_OrderedContacts (listIndex, contactId)
VALUES (0,13259), (1,12472), (2,12422);

SELECT * FROM tContacts
JOIN temp_OrderedContacts ON (tContacts.conIdContact = temp_OrderedContacts.contactId)

DROP TABLE temp_OrderedContacts;

Answer 1

A Postgres solution would be to put all the IDs into an array, then use something like this:

select t.*, il.listindex
from contacts t
   join unnest(array[13259, 12472, ..., 12422]) with ordinality as il(id, listindex) 
     on t.conidcontact = il.id
order by il.listindex;

^{(The above is actually standard ANSI SQL - but to my knowledge only Postgres and HSQLDB support that)}

The array could also be passed as a parameter in a prepared statement (or whatever the term for that is in the programming language you are using).

---

If you can build a dynamic query, another option would be to use a row value constructor:

select t.*, il.listindex
from contacts t
   join ( 
      values 
        (1, 13259), 
        (2, 12472),  
        ..... 
        (568, 12422)
  ) as il(listindex, id) on t.conidcontact = il.id
order by il.listindex;

^{(The above is 100% standard ANSI SQL)}