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.
