'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?

mysqlsql-order-bysql-in


Solution 1:[1]

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.

Solution 2:[2]

I would encourage you to watch this link (subselect order timestamp) The problem you have is as you thought, even if the subselect is ordered then it gets out of place on the main query, a Join would be useful for this cases.

Hope it helps you.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Allan Vital
Solution 2 Community

Related Questions