Get value with corresponding highest int with GROUP BY

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?

>Solution :

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.