Using Union All and Order By in MySQL

问题:

I've 2 tables:

create table advertised_products(id int,title varchar(99),timestamp timestamp);
insert advertised_products select 1,'t1',curdate();

create table wanted_products(id int,title varchar(99),timestamp timestamp);
insert wanted_products select 1,'t1',now();

I'm using this query to get the records:

(
SELECT 
    ap.*,
    'advertised'  as type 
FROM advertised_products as ap
)
union all
(
SELECT 
    wp.*,
    'wanted' as type 
FROM wanted_products as wp
)
ORDER BY timestamp desc limit 3

But it gives error:

Column 'timestamp' in order clause is ambiguous

How can i sort this?

回答1:

Wrap it in a subquery.

SELECT s.*
FROM
    (
        SELECT  ap.*, 'advertised'  as type 
        FROM advertised_products as ap
          union all
        SELECT  wp.*, 'wanted' as type 
        FROM wanted_products as wp
    ) s
ORDER BY s.timestamp desc 
limit 3

回答2:

Error lies in here,

 "ORDER BY timestamp desc limit 3"

as you are combining the two tables query must know which fields are you using in which table.clearly that you are missing the table reference in your "orderby" clause

mention the table name/alias of the table name like below

 "ORDER BY your_table_name.timestamp desc limit 3"