Using Union All and Order By in MySQL

Question

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?

What version of MySQL are you using? Your queries have no errors and works perfectly fine over here (version `5.5.10`). — Pacerier, May 05 '15 at 06:51

score 3 · Accepted Answer · answered Oct 15 '12 at 09:25

3

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

answered Oct 15 '12 at 09:25

John Woo

258,903
69
498
492

@JohnWoo, I must be missing something. [Why is wrapping in subqueries](http://stackoverflow.com/questions/12892688/using-union-all-and-order-by-in-mysql#comment48207339_12892688) needed? – Pacerier May 05 '15 at 06:52

score 0 · Answer 2 · answered Oct 15 '12 at 09:32

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"

Using Union All and Order By in MySQL

2 Answers2

Linked

Related