Keep order in WHERE IN (subquery)

Question

I have a problem about keeping the order in the subquery with WHERE IN clause.

I am using this following query with 2 tables listed below.

SELECT * FROM user_list WHERE uid IN (
        SELECT uid from user_history ORDER BY timestamp DESC
) LIMIT 5

Table - user_list

uid name    surname
001 Alpha   Apples
002 Bravo   Butter
003 Charlie Charlie
004 Delta   Duff
005 Echo    Edward

Table - user_history

uid timestamp
003 0000-00-00 00:00:00
001 0000-00-00 00:00:01
005 0000-00-00 00:00:02

The expected result is (ORDER BY timestamp DESC)

005 Echo    Edward
001 Alpha   Apples
003 Charlie Charlie

But its result as this

001 Alpha   Apples
003 Charlie Charlie
005 Echo    Edward

The ORDER BY clause is ignored and still order by primary key (uid).

I am wondering is there any way to keep the order from subquery?

Since an order in a subquery does not make any sense why move the order by to the outer query? — juergen d, Mar 31 '19 at 17:15
According to [this question](https://stackoverflow.com/questions/26372511/mysql-order-by-inside-subquery), it depends on your mysql version. Different versions behaves differently. And finally we don't expect your desired result in standard SQL. — x01saa, Mar 31 '19 at 17:58

score 2 · Accepted Answer · answered Mar 31 '19 at 17:15

2

no need any subquery use join and try like below

SELECT ul.* FROM user_list ul join user_history uh
on ul.uid=uh.uid
order by uh.timestamp desc
limit 5

subquery order by does not have any affect in the main query output

answered Mar 31 '19 at 17:15

Zaynul Abadin Tuhin

31,407
5
33
63

score 0 · Answer 2 · answered Mar 31 '19 at 17:18

0

First you have to join the user_history then order it

SELECT * FROM user_list WHERE uid IN (
        SELECT user_history.uid from user_history join user_list where  
 user_list .uid = user_history.uid  ORDER BY user_history.timestamp DESC
) LIMIT 5

answered Mar 31 '19 at 17:18

Shadiqur

490
1
5
18

score 0 · Answer 3 · answered Mar 31 '19 at 17:22

Below query can give you desired result. Use join on two tables and then sort the colums by timestamp in main query.

Sortin inside subquery will give uid sorted inside in clause but main query by default gives sorted by id .

SELECT u.uid,  u.name , u.surname FROM user_list u, user_history h  WHERE u.uid IN (
        SELECT uid from user_history 
) and u.uid = h.uid order by h.timestamp desc

score 0 · Answer 4 · answered Mar 31 '19 at 17:36

I think you want:

SELECT ul.*
FROM user_list ul JOIN
     (SELECT uh.uid, MAX(timestamp) as max_timestamp
      FROM user_history uh
      GROUP BY uh.uid
      ORDER BY MAX(timestamp) DESC
      LIMIT 5
     ) uh
     ON ul.uid = uh.uid
ORDER BY max_timestamp DESC;

There may be duplicates in user_history (quite likely based on the name). You seem to want the five most recent records. So, this aggregates the data to get the five most recent records, then joins to user_list and returns the rows in descending order by timestamp.

Keep order in WHERE IN (subquery)

4 Answers4