I have multiple queries to get data marked under different categories, with it returning the last X rows from before a given datetime. The max timestamp value will be different for each of the categories
SELECT * FROM table WHERE category="a" AND timestampCol < timestampValue1 ORDER BY timestampCol DESC LIMIT 3;
SELECT * FROM table WHERE category="b" AND timestampCol < timestampValue2 ORDER BY timestampCol DESC LIMIT 3;
SELECT * FROM table WHERE category="c" AND timestampCol < timestampValue3 ORDER BY timestampCol DESC LIMIT 3;
...
I would like to optimise this to run as a single query as it can be pulling from up to 8 categories at a time which is a lot of queries to be running.
I need the database to return a set number from each category. It would be nice to customize the LIMIT per category however this is not essential as long as I am guaranteed to have data return from all the categories.
I tried the query below, which will return the first 9 rows. But they could all be from category A
, which is not what I want.
SELECT * FROM table WHERE
(category="a" AND timestampCol < timestampValue1)
OR (category="b" AND timestampCol < timestampValue2)
OR (category="c" AND timestampCol < timestampValue3)
ORDER BY timestampCol DESC LIMIT 9;
...
Using GROUP BY on category would only return me one of each category instead of 3, unless there is a nice way I can get that to work?
How can I do this?
You need to sign in to view this answers
Leave feedback about this