Search code examples
mysqlsql-order-bysql-in

MySQL Order By timestamp using IN clause

I have the below query and it works fine at returning results based on the set timestamp. 

SELECT * FROM catalog WHERE part IN (SELECT part FROM query_data WHERE timestamp >= '2015-02-02') LIMIT 10

What I would like to do is get the results from the above but ORDER BY timestamp in DESC order. Something like this, but it doesn't work. The same values are returned, but not in DESC order based on the timestamp.

SELECT * FROM catalog WHERE part IN (SELECT part FROM query_data WHERE timestamp >= '2015-02-02' ORDER BY timestamp DESC) LIMIT 10

Thoughts? The timestamp column is only found in the query_data table. Not sure if this is causing the issue or not?

Solution

  • I believe this will work:

    SELECT * FROM catalog c INNER JOIN query_data q ON c.part = q.part WHERE q.timestamp >= '2015-02-02' ORDER BY timestamp DESC;

    The main problem with your approach is that you are ordering the subquery. Using a join and "order by" outside should fix it.