SQL: how to remove duplicates when doing complex order by

Question

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 I get this 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?

Well a given user may have multiple levels. _Which_ level should be used for ordering? — Tim Biegeleisen, Aug 24 '23 at 13:12
Joins do produce multiple rows for the driving tables. That's how they work. It seems you want to filter out some rows. Please add a few rows of sample data and the expected result. — The Impaler, Aug 24 '23 at 13:15
`order by` happens after everything else is done. At the point that `order by` runs, there is no column `l.level`. You merely have the aggregate result set with `u.id` to work with. Perhaps you want to order by the `max(l.level)` that the user might belong to? Think about which of the competing `l.level` values for any particular user that would cause the user to be ranked the way you wish, and use the appropriate aggregate formula to achieve that. — JNevill, Aug 24 '23 at 13:16

score 1 · Answer 1 · answered Aug 24 '23 at 13:16

You can do the calculation in a CTE and the query that CTE and removing the column you do not want in your result set

WITH _CTE AS (
    SELECT u.id,
    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 OrderID,
    u.last_online_at
FROM users u
INNER JOIN users_game_level l ON l.user_id = u.id
)
Select id
from _CTE
GROUP BY u.id
order by OrderID ASC,last_online_at DESC, id DESC

Your query, after compiling the CTE, is almost identical to the OP's original query, and would yield the same error. — Tim Biegeleisen, Aug 24 '23 at 13:37

score 1 · Accepted Answer · answered Aug 24 '23 at 13:20

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;

SQL: how to remove duplicates when doing complex order by

2 Answers2