postgres order by not working properly with DISTINCT ON

August 18, 2023

I have a postgres table with the structure(id: int, idarray: int[], title: text, ident: text). I want to search the table with something like title LIKE …(i didnt include that because its not relevant for the issue) and then sort out the duplicates. these are defined by the idarray.

 id | idarray | title | ident 
----+---------+-------+-------
  2 | {2}     | test  | x
  3 | {3}     | test  | y
  4 | {4}     | test  | z
  1 | {5,1}   | test  | a

SELECT DISTINCT ON (idarray)
  *
FROM info
ORDER BY
  idarray,
  CASE
    WHEN ident = 'a' THEN 1
    WHEN ident = 'y' THEN 2
    ELSE 3
  END,
  CASE
    WHEN ident IN ('a', 'y') THEN id
    ELSE -id
  END DESC;

The problem is that it should order the endresult by id. 1 Should be on the top and not at the bottom.

The expected behavour is that it should order by the first given argument(in this case id). It should prioritize in each group with this order a, y, biggest id.