0

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?

1
SELECT *
FROM entries
WHERE category = 'B'
ORDER BY id DESC LIMIT 1
| improve this answer | |
  • This doesn't work; the post mentions that multiple categories need to be selected so a GROUP BY is necessary, and the ORDER BY happens after the GROUP BY, and therefore the highest entry for each category will not be returned – user218609 Nov 13 at 17:45
  • 1
    @user218609 If so provide adequate sample data which demonstrates all possible facts combination and desired result for it. The best way - provide online fiddle with this data. – Akina Nov 13 at 17:56
  • All the information needed is contained in the question. If you need clarifications, add a comment to the question – user218609 Nov 13 at 21:50
  • 1
    @user218609 All the information needed is contained in the question. My query fully matches your source data and desired output provided in a question. If you are not glad then your information is partial. – Akina Nov 14 at 8:08
  • READ THE QUESTION and you will see "I need to select multiple categories". Your query does not work. Your answer is wrong. We all make mistakes, the only difference is whether or not you can admit them. And you can't. – user218609 Nov 16 at 22:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.