1

I want to find a user's favorite music list. First step, I query all id from user's favorite table. In the next step I query the music list by id from songs table. But now I want to sort rows by the timestamp column favorite.added_at desc.

I do not want to join table, so I want to figure out if it's possible to sort by the input id in the in query.
For example:

-- the 3087 is first record
select *
from songs s 
where id in (3087,5122)

-- the 5122 is first record
select *
from songs s 
where id in (5122,3087)

I have tried in PostgreSQL 13, and the row with id = 3087 was always first. Seems like PostgreSQL does not fetch rows by the sequence of values in the IN list?

2
  • Is the query being passed to PostgreSQL as a complete string, or are you using prepared statements when querying?
    – matigo
    Commented Sep 26, 2021 at 8:06
  • I execute this in DBeaver, the orm framework when running app is rust diesel. Is this effect the query result @matigo
    – Dolphin
    Commented Sep 26, 2021 at 8:10

4 Answers 4

2

There is a fundamental flaw with your approach, in that you first select the favorites and then fetch the song details in a second query. Not only are you lacking a good ORDER BY clause for the second query, but splitting a task like that in two queries causes unnecessary work.

You can improve performance and solve your problem in one step by solving the complete problem in a single query that joins both tables. Then the ORDER BY clause would come natural.

2

Sort order

Seems like PostgreSQL does not fetch rows by the sequence of values in the IN list?

The order of values passed to IN is insignificant. Postgres is a liberty to return rows in arbitrary order unless determined with ORDER BY.

You could pass a set and add an "ordering" column.
But it's much simpler to pass an array and work with the given order of elements. Use unnest() and append WITH ORDINALITY:

-- 3087 first
SELECT s.*
FROM   unnest('{3087,5122}'::int[]) WITH ORDINALITY AS i(id, ord)
JOIN   song s USING (id)
ORDER  BY ord;

db<>fiddle here

See:

Another important difference: while duplicates in an IN list (or set) are removed, they are kept as given when joining to an unnested array.

Proper solution

Assuming both tables (favorite and songs) reside in the same DB, it would be much better to run a single query:

SELECT s.*
FROM   favorite f
JOIN   song s ON s.id = f.song_id
WHERE  f.user_id = 123
ORDER  BY f.added_at DESC;

Then you can order by favorite.added_at directly. And it's a single round-trip to the DB server.

1

If I've understood you correctly, you want to do something like this (a fiddle with all of the code below is available here):

CREATE TABLE song
(
  song_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  song_name TEXT NOT NULL,
  song_artist TEXT NOT NULL
  --
  -- more fields
  --
);

populate it:

INSERT INTO song (song_name, song_artist) VALUES 
('x song  1', 'artist  1'),
('y song  2', 'artist  2'),  -- the reason for the letters is that songs 
('k song  3', 'artist  3'),  -- in a collection are unlikely to be in 
('c song  4', 'artist  4'),  -- alphabetical order
('b song  5', 'artist  5'),
('l song  6', 'artist  6'),
('m song  7', 'artist  7'),
('a song  8', 'artist  8'),
('a song  9', 'artist  9'),
('b song 10', 'artist 10');

So now, if you run a query like this:

SELECT
  s.song_name, s.song_artist
FROM 
  song s
WHERE s.song_id IN (3, 5)
UNION
SELECT
  s.song_name, s.song_artist
FROM 
  song s
WHERE s.song_id IN (2, 8);

You will obtain a result like this(1):

song_name   song_artist
y song  2   artist  2
b song  5   artist  5
a song  8   artist  8
k song  3   artist  3

where (3, 5) and (2, 8) are "mixed up". NOTE: you are not guaranteed to obtain this exact result on any run of the fiddle or on your own system - the SQL Standard says that without an ORDER BY clause in a query, the results can be returned in any order!

On a table with thousands of records (with UPDATEs and DELETEs thrown into the mix), predicting the sort order of any resultset (without an ORDER BY) is going to be impossible.

(1) I was surprised by this result - I expected the query to return results in PRIMARY KEY order, but it doesn't - at least not today on this particular fiddle - proving that ORDER BY always needs to be specified. Just because a non-specified order is returned today (just good luck? whatever?), doesn't mean that your results will be returned in that order tomorrow!

So, you can do something like this:

SELECT
  'query 1' AS the_query, s.song_name, s.song_artist
FROM 
  song s
WHERE s.song_id IN (3, 1, 5)
UNION
SELECT
  'query 2', s.song_name, s.song_artist
FROM 
  song s
WHERE s.song_id IN (2, 8)
ORDER BY the_query, song_name;

Result:

the_query   song_name   song_artist
query 1     b song  5   artist  5
query 1     k song  3   artist  3
query 1     x song  1   artist  1
query 2     a song  8   artist  8
query 2     y song  2   artist  2

Which is correct (note the sequences (b, k, x) and (a, y).

I introduced an "artificial" sorting key into this query - the the_query string which can be used to separate the results of the different queries in the ORDER BY. This can be useful in cases such as this!

Or, if you simply want to sort by song_id within the different queries:

SELECT
  'query 1' AS the_query, s.song_id, s.song_name, s.song_artist
FROM 
  song s
WHERE s.song_id IN (3, 1, 5)
UNION
SELECT
  'query 2', s.song_id, s.song_name, s.song_artist
FROM 
  song s
WHERE s.song_id IN (2, 8)
ORDER BY the_query, song_id;

Result:

the_query   song_id     song_name   song_artist
query 1           1     x song  1   artist  1
query 1           3     k song  3   artist  3
query 1           5     b song  5   artist  5
query 2           2     y song  2   artist  2
query 2           8     a song  8   artist  8

Again, because we've specified the ORDER BY, we get what we asked for (it does what it says on the tin!).

EDIT:

On rereading the question, it appears that the OP wants the order of records to reflect the order of the song_ids in the IN(...) clause (fiddle here).

This can be done as follows:

Imagine we want the songs with ids 5, 8 and 2 to be presented in that order in the resultset.

So, we might try:

SELECT
  s.song_name, s.song_artist
FROM 
  song s
WHERE s.song_id IN (5, 8, 3);
-- ORDER BY s.song_id DESC;

But, the result (on my fiddle today) is:

song_name   song_artist
k song  3   artist  3
b song  5   artist  5
a song  8   artist  8

So, it's in order of song_id - but there is no guarantee that this will happen next week, tomorrow or even in the next second. I can guarantee or reverse this adding the ORDER BY s.song_id ASC/DESC.

PostgreSQL will resuse space in pages when records are deleted so records may well not be in PRIMARY KEY order on disk. Unlike some other systems, PostgreSQL does not have clustered indexes.

If you want a cast-iron guarantee that the records will appear as you put them into the list in the IN (...) clause, then you have to do the following:

SELECT 
  s.song_id, 
  s.song_name, 
  s.song_artist, 
  t.id, 
  t.id_sort_order
FROM
  song s
JOIN
(
  VALUES(1, 5), (2, 8), (3, 2)
) AS t (id_sort_order, id) 
ON s.song_id = t.id
ORDER BY t.id_sort_order;

You use the VALUES clause to construct an "artificial" table with two fields - one is your id_sort_order (1,2,3) and the other is your id which is your desired sort order (5,8,3) and then JOIN on the desired sort order to retrieve the details you want about songs (5,8,3), but you sort by (1,2,3), thereby obtaining your desired order in your final result set.

Result:

song_id     song_name   song_artist     id  id_sort_order
      5     b song  5   artist  5        5              1
      8     a song  8   artist  8        8              2
      2     y song  2   artist  2        2              3

The methods outlined above are the only ones which will guarantee your sort order.

3
  • The "surprise" in your UNION query goes away when you use UNION ALL (as appropriate). But that's a tangent to the actual question, as you clarified yourself. Commented Sep 27, 2021 at 1:21
  • @ErwinBrandstetter - why is UNION ALL appropriate? I would think that the context of this query implies that any duplicate would be an error - therefore UNION is appropriate in this case? Is there any way of making VALUES into a set-returning function? See the last snippet here - the query always returns in the order given in the VALUEs clause. I know that a few tests on a fiddle do not a summer make, but just wondering? Or is that a new question?
    – Vérace
    Commented Sep 27, 2021 at 9:09
  • Multiple unioned SELECT would only make sense to keep multiple lists apart (I interpreted), hence UNION ALL, but that's all moot now. You can't attach WITH ORDINALITY to a VALUES expression directly - if that's what you are after. Else, yes, new question! ;) Commented Sep 27, 2021 at 12:00
0

You can use ORDER BY clause with a CASE:

SELECT * FROM `songs`
ORDER BY 
  CASE
    WHEN `id` = 3087 THEN 0
    WHEN `id` = 5122 THEN 1
    ELSE `id` NOT IN (3087, 5122)
  END
ASC

In this example, The song with ID 3087 will be the first record. And the song with ID 5122 will be the second. Then other records

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.