CODEWITHSUNDEEP
mysql
Pervaiz Iqbal
Pervaiz Iqbal

Reputation: 326

mysql - Get top 5 records for each group of grouped results

select *,COUNT(feed_id) from
 (SELECT feed_contents.*, feed.feed_url, feed.lang_direction, feed.feed_title 
    FROM feed_contents 
    INNER JOIN feed ON feed_contents.feed_id = feed.feed_id 
    INNER JOIN user_feeds ON feed_contents.feed_id = user_feeds.feed_id 
    WHERE user_feeds.user_id = 13 
    AND DATE(feed_contents.content_date) >= CURDATE() - INTERVAL 90 DAY 
    ORDER BY feed_contents.content_date desc) as tbl 
group by feed_id
order by content_date desc 
limit 0,20

i have this query to get results from multiple tables, in result it returning one record against feed_id, i want 5 records against each feed_is

output is like http://screencast.com/t/HHxNOOSdSX4S

i want max 5 from each

Upvotes: 0

Views: 74

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use variables for this:

SELECT feed_id, content_date, feed_url, lang_direction, feed_title
FROM ( 
  SELECT feed_id, content_date, feed_url, lang_direction, feed_title,   
         @rn := IF(@fid = feed_id, @rn + 1,
                   IF(@fid := feed_id, 1, 1)) AS rn
  FROM (
    SELECT feed_contents.feed_id, 
           feed_contents.content_date,
           feed.feed_url, 
           feed.lang_direction, 
           feed.feed_title 
    FROM feed_contents 
    INNER JOIN feed ON feed_contents.feed_id = feed.feed_id 
    INNER JOIN user_feeds ON feed_contents.feed_id = user_feeds.feed_id 
    WHERE user_feeds.user_id = 13 AND 
          DATE(feed_contents.content_date) >= CURDATE() - INTERVAL 90 DAY) AS tbl 
  CROSS JOIN (SELECT @rn := 0, @fid := 0) AS vars
  ORDER BY feed_id, content_date DESC) AS s
WHERE s.rn <= 5

Variable @rn is used to enumerate records within each feed_id partition. Once feed_id value changes, @rn is being reset to 1, so as to start counting for the next partition. Records are numbered in descending order according to field content_date.

Upvotes: 1

Related Questions