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