There is a news site, news is divided into sections. How to display in one query N records for each section? Read here

I tried this query:

SELECT a.* FROM content_content AS a LEFT JOIN content_content AS a2 ON a.news_section_id = a2.news_section_id AND a.pub_date <= a2.pub_date GROUP BY a.title HAVING COUNT(*) <= 3 ORDER BY a.news_section_id, a.pub_date DESC;

But after 10 minutes of inactivity, Muscle loses connection. I expected almost instant performance. What am I doing wrong?

  • There is much written, and in our opinion. The question pops up often. In mysql, you can renumber the records in groups using local variables, and then output those who have a number less than N. It is even possible to have it in the FAQ. I answered such questions more than once. Now too lazy to look. If you need to - find - alexlz
  • @alexlz is also in Russian) only the request does not work - Anton Feoktistov
  • Well, from my answers hashcode.ru/questions/305658/... If you have questions - ask. Pay attention to the installation of @i in 1 when changing gallery - alexlz

1 answer 1

The following query returns 10 rows from each section:

 SELECT * FROM( SELECT *, /*Ссли Π² ΠΏΡ€Π΅Π΄Ρ‹Π΄ΡƒΡ‰Π΅ΠΉ строкС сСкция Π±Ρ‹Π»Π° Ρ‚Π° ΠΆΠ΅, Π½ΡƒΠΌΠ΅Ρ€ΡƒΠ΅ΠΌ строки Π²Π½ΡƒΡ‚Ρ€ΠΈ сСкции*/ /*ΠΈΠ½Π°Ρ‡Π΅ присваСваСм счСтчику 1 (Ρ‚.Π΅. Π½Π°Ρ‡ΠΈΠ½Π°Π΅ΠΌ Π½ΡƒΠΌΠ΅Ρ€Π°Ρ†ΠΈΡŽ Π½ΠΎΠ²ΠΎΠΉ сСкции)*/ IF(@last_session_id=news_section_id, @I:=@I+1, @I:=1)N, /*присваСваСм ΠΈΠ΄ сСкции Π² ΠΏΠ΅Ρ€Π΅ΠΌΠ΅Π½Π½ΡƒΡŽ*/ @last_session_id := news_section_id FROM content_context, /*ΠΈΠ½ΠΈΡ†ΠΈΠ°Π»ΠΈΠ·ΠΈΡ€ΡƒΠ΅ΠΌ Π»ΠΎΠΊΠ°Π»ΡŒΠ½Ρ‹Π΅ ΠΏΠ΅Ρ€Π΅ΠΌΠ΅Π½Π½Ρ‹Π΅*/ (SELECT @last_session_id := null, @I := 0)T /*сортируСм ΠΏΠΎ сСкции, Π·Π°Ρ‚Π΅ΠΌ Π΄Π°Ρ‚Π΅ Π² ΠΎΠ±Ρ€Π°Ρ‚Π½ΠΎΠΌ порядкС*/ ORDER BY news_section_id, pub_date DESC )T /*Π²Ρ‹Π²ΠΎΠ΄ΠΈΠΌ ΠΏΠΎ 10 строк ΠΈΠ· ΠΊΠ°ΠΆΠ΄ΠΎΠΉ сСкции*/ WHERE N <= 10