2

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;
2
  • Unrelated, but: dba.stackexchange.com/q/154251/1822
    – user1822
    May 3, 2018 at 7:33
  • i just used the tablenames from our database (designed 25 years back) to be quicker in testing the query ;-) - i'm also not naming classes ClassXYZ, but sometimes variables make sense if they are named like what they contain (eg ui can have a Label and an Textfield for one property -> lblUsername and txtUsername are the two ui components that relate to the UserName-data
    – lumo
    May 3, 2018 at 7:47

1 Answer 1

5

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)

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.