Why ORDER BY works only when I gave an alias name for the column, but didn't work just as colum-CodePudding

This code cannot be executed, showed an error like

column "o.total_amt_usd" must appear in the GROUP BY clause or be used in an aggregate function

SELECT a.name, MIN(o.total_amt_usd) 
FROM accounts a
JOIN orders o ON a.id = o.account_id
GROUP BY a.name
ORDER BY o.total_amt_usd 
LIMIT 3

But after I use an alias, it worked:

SELECT a.name, MIN(o.total_amt_usd) small
FROM accounts a
JOIN orders o ON a.id = o.account_id
GROUP BY a.name
ORDER BY small 
LIMIT 3

Could anybody explain a little bit about this, please?

Both logically make sense to me. But one of them is not working.

Thanks a lot.

CodePudding user response：

You can't order by o.total_amt_usd since it's not available in the result set (after grouping on name).

You need to order by a grouped field or using an aggregate function like MIN. In this case you'll want to order by MIN(o.total_amt_usd) which is essentially what you are doing after using the alias in the order-clause.

CodePudding user response：

Think about what you're asking of the first query and perhaps try a visual example by hand.

Imagine a table with just 4 rows, John has two rows with amounts of 50 and 20, Bob has two rows with amounts of 30 and 60.

You are asking for each unique name and the corresponding minimum amount, so the results are naturally John:20, Bob:30.

By asking to order your results by referring specfically to every row's Total (and not the aggregated total) you are saying, order my two rows by looking at all four rows, which means John could go both before Bob and after Bob given 20 is less than 30 and 50 is greater than 30.

You might look at the data visually and see the "correct" order, however for the query engine this makes no sense, you can only prioritise the resulting two rows based on their aggregated values, therefore you must order by those aggregated values, either using that column's alias or using the same expression. You cannot order by non-aggregated columns.