Using Union All and Order By in MySQL

Question

I have 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 use this query to get entries:

 ( 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 this gives an error:

The "timestamp" column in the order clause is ambiguous

How can I sort this?

Answer 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 

Answer 2

Here is the error

  "ORDER BY timestamp desc limit 3" 

since you combine these two tables, the query should know which fields you use in the table. Obviously, you are missing a table reference in your orderby clause.

provide the table name / alias of the table table name as shown below

  "ORDER BY your_table_name.timestamp desc limit 3"