How to choose a single query last 5 records of each category in MySQL?


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
6 views
Hello.

Let's say we have a table with the following structure: id, cid, title.

Maybe there is some elegant way to choose one query last 5 records of each category(cid)?
by | 6 views

6 Answers

0 like 0 dislike
by
0 like 0 dislike
Try. All measurements on the table with 5000 entries, random cid from 1 to 10, primary id, cid index.
\r
In PostgreSQL you can query
\r
SELECT a.* FROM somedata AS a WHERE a.id IN ( SELECT b.id FROM somedata AS b WHERE b.cid = a.cid ORDER BY b.id DESC LIMIT 5 ) ORDER BY a.cid DESC, a.id DESC

But the query is slow (0.125 sec) and the execution time increases in direct proportion to the count of records and categories. MySQL does not support LIMIT in subqueries, go ahead. Master terrible monster:
\r
SELECT a.* FROM somedata AS a WHERE a.id IN ( SELECT id FROM somedata AS b WHERE b.cid = a.cid AND (SELECT COUNT(*) FROM somedata AS c WHERE c.id >= b.id AND c.cid = b.cid) <= 5 ) ORDER BY a.cid DESC, a.id DESC

It is, of course, right, received the correct data, but the execution of such a request... took 15.87 seconds. Not want, right? :)
\r
The most productive turned out to be aesthetically ugly ugly. The bonding in PHP, the queries for each category in a single by using a UNION:
\r
(SELECT * FROM somedata WHERE cid = 1 ORDER BY id DESC LIMIT 5) UNION (SELECT * FROM somedata WHERE cid = 2 ORDER BY id DESC LIMIT 5) UNION (SELECT * FROM somedata WHERE cid = 3 ORDER BY id DESC LIMIT 5)

etc. to cid = 10, and lo and behold: the query runs on MySQL for 0.002 seconds, giving the desired result.
\r
Although, maybe I missed some obvious solution with acceptable performance. If you find – tell us :)
by
0 like 0 dislike
order by `id` desc limit 5?
by
0 like 0 dislike
What we consider first, what last? id?
by
0 like 0 dislike
Try:
\r
SELECT `t1`.`id`, `t1`.`cid`, `t1`.`title`, COUNT(*) as `counter` FROM `test` `t1` JOIN `test` `t2` ON `t1`.`cid` = `t2`.`cid` AND `t1`.`id` >= `t2`.`id` GROUP BY `t1`.`cid`, `t1`.`id` HAVING `counter` <= 5 ORDER BY `t1`.`cid`, `t1`.`id`;
by
0 like 0 dislike
5 select points and 1 request that includes the 5 selects with UNION ALL is the best solution for a work project.
by

Related questions

0 like 0 dislike
2 answers
0 like 0 dislike
1 answer
asked Jun 4, 2019 by khodos_dmitry
0 like 0 dislike
1 answer
asked May 10, 2019 by ShelestovAnt
110,608 questions
257,186 answers
0 comments
25,519 users