Mysql – Rewriting query to move subquery out of FROM clause

greatest-n-per-groupMySQLmysql-5.7

Suppose I have an entries table that looks something like this:

| id | category | text | user_id |
|----|----------|------|---------|
| 1  | A        | z    | 22      |
| 2  | B        | y    | 23      |
| 3  | B        | x    | 24      |
| 4  | C        | w    | 25      |

id is unique; none of the other columns are

I want to get the row for specified category(ies) that have the highest ID. So in this example if I'm search for category B I'd expect it to return 3,B,x,24

There are several more columns in the table, but only those three are shown for brevity. Other columns exist which have to be joined, e.g. to get user info.

I have the following query, which works:

SELECT *
FROM (
         SELECT MAX(id) as id
         FROM entries
         WHERE category = 'B'
         GROUP BY category
     ) as tmp
         LEFT JOIN entries e ON e.id = tmp.id
         LEFT JOIN users u ON (e.user_id = u.id)

An OR condition can be added to the WHERE clause when I need to select multiple categories, and that works.

However my ORM doesn't support subqueries in the FROM clause. I managed to get it to work with WHERE ANY, but the query is 4.5 times slower:

SELECT *
FROM entries
WHERE id = ANY 
    (
        SELECT MAX(id) as id
        FROM entries
        WHERE category = 'B'
        GROUP BY category
    );

Replacing ANY with ALL yields the same data but ALL takes at least 5 times longer than ANY.

Is there another syntax that runs as fast as the first but with some other syntax?

Best Answer

SELECT *
FROM entries
WHERE category = 'B'
ORDER BY id DESC LIMIT 1
Related Question