I have 2 tables:

• quests (2 columns: player, quest)
• dimension_quest (2 columns: quest, order)

quests dimension_quest

For all players, I'd like to have the quest with the highest "order" column. I want all 3 columns (player, quest, order)

Expected output

Note that a quest has an unique "order" value in dimension_table. I cannot really aggregate the quest strings.

What I could do is this

SELECT player, max(dim.order) FROM quests qu
LEFT JOIN dimension_quest dim ON qu.quest = dim.quest
GROUP BY player

THEN JOIN again with dimension_quest to have the quest name. But isn't there a better way to have directly the quest name in the aggregated query, instead of joining dimension_quest twice?

CodePudding user response：

But isn't there a better way to have directly the quest name in the aggregated query, instead of joining dimension_quest twice?

No. In standard SQL, the selection list for an aggregate query may refer only to grouping columns and aggregate functions of the groups. Your quest columns are neither.

Some SQL dialects allow other columns to be selected in aggregate queries, but those dialects generally do not specify from which row of each group the corresponding values for such columns will be selected. That's ok in some cases, but it is not sufficient for your purposes.

CodePudding user response：

Here's a way to do it without an extra join using row_number().

select  player, quest, "order"
from    (
        select  player, q.quest, "order"
                ,row_number() over(partition by player order by "order" desc) as rn
        from    quests q join dimension_quest dq on dq.quest = q.quest
        ) t
where   rn = 1

Fiddle