what is the difference between those 2 queries below?

========The first query==========

SELECT 1 AS question_id, COUNT(id) AS answer 
FROM annual_payments 

UNION

SELECT 2 AS question_id, COUNT(DISTINCT user_id) AS answer 
FROM annual_payments 

UNION

SELECT 3 AS question_id, COUNT(id) AS answer 
FROM annual_payments

WHERE STATUS = "paid" AND amount >= 100

UNION

SELECT 4 AS question_id, product AS answer
FROM annual_payments
WHERE status = 'paid'

ORDER BY SUM(amount)

LIMIT 1

When I executed this query, I got the error: Error 1054: Unknown column 'amount' in 'order clause'

==========The second query===========

SELECT 1 AS question_id, COUNT(id) AS answer 
FROM annual_payments 

UNION

SELECT 2 AS question_id, COUNT(DISTINCT user_id) AS answer 
FROM annual_payments 

UNION

SELECT 3 AS question_id, COUNT(id) AS answer 
FROM annual_payments
WHERE STATUS = "paid" AND amount >= 100

UNION

SELECT *
FROM
(SELECT 4 AS question_id, product AS answer
 FROM annual_payments
 WHERE status = 'paid'
 ORDER BY amount
 LIMIT 1 ) x

=========================

I got the answer with the second query.

The difference between those 2 queries is to use "select *"

Why I got an error with the 1st query? What is the difference between those 2?

CodePudding user response：

When you combine multiple queries with union, the column aliases are determined by the first query.

An order by clause applies to the entire result set and must refer to the columns as defined by the first query in the set of unioned queries.

Your second example works because you are not applying order by to the unioned results, you are using it within the context of a derived table which is fine, it applies to that sub-query only.

CodePudding user response：

1 vs 2 is completely different, i think 1st cannot sort by sum(amount)