SQL: how to remove duplicates when doing complex order by

August 24, 2023

Consider the following query:

SELECT u.id FROM users u
INNER JOIN users_game_level l ON l.user_id = u.id
GROUP BY u.id
ORDER BY
    CASE
        WHEN l.level = 'BEGINNER' AND l.game_code = 'arcade' THEN 1
        WHEN l.level = 'MEDIUM' l.game_code = 'race' THEN 2
        WHEN l.level = 'PROF' THEN 3
    END ASC,
    u.last_online_at DESC,
    u.id DESC
LIMIT 100

users is related to users_game_level based on one-to-many relationship.

Basically I need all the users in an order, depending on some conditions from the second table.

But it gives me an error :

"ERROR: column "l.level" must appear in the GROUP BY clause or be used in an aggregate function".

The same error goes for l.game_code and u.last_online_at. And if I add all these to the GROUP BY it returns duplicates nevertheless.

What am I doing wrong?

>Solution :

A given user, by definition, may have multiple levels. Hence, it makes no sense to be ordering by a single level, as it isn’t clear which of several level values should be used. Assuming you wanted to order by the highest level achieved, you could try:

SELECT u.id
FROM users u
INNER JOIN users_game_level l ON l.user_id = u.id
GROUP BY u.id
ORDER BY
    MAX(CASE WHEN l.level = 'BEGINNER' AND l.game_code = 'arcade' THEN 1
             WHEN l.level = 'MEDIUM' l.game_code = 'race' THEN 2
             WHEN l.level = 'PROF' THEN 3 END),
    u.id DESC;