The LIMIT operand in MySQL serves the purpose to limit the number of rows returned in the result set. Often, however, there is need to summarize results according to certain categories and when presenting such results on a summary page, often there is need to limit the number of rows per category. MySQL's LIMIT clause, however, cannot be applied in this case and Group Limit problem arises.

One solution to this problem is to load arrays of limited categories within PHP, presenting them one after another on some summary page. Sometimes that is not adequate and another solution is required using temporary summary tables. Such tables can be in memory only, created each time a summary is created, or can be permanent, updated with each change to the tables that contain data for such summary table. The later is recommended for heavy production environments that cannot afford summary table creation on each summary page view.

These summary tables are relational and can be made of fixed size reducing overhead and speed degradation with many updates to such tables.

How does one such table look? Let's say for example that you have a table of blog posts and you need to create a summary of last 10 posts per each category.

table_posts

• primary_key
• category_id
• author_id
• post_title
• post_body
• time_stamp
• ... other fields relevant for the post

The relational summary table would then look like this:

table_posts_summary

• category_id
• post_id (primary_key in table_posts)
• time_stamp (timestamp of the post)

Now, each time an author submits new post, you need to do a couple of things, in steps:

• Insert entire post into table_posts, with given category
• If there are any rows for given category in table_posts_summary, delete them
• Select last 10 posts from table_posts, with given category, ordered by timestamp descending
• Insert category_id, post_id and timestamp for each of the last 10 posts you just selected

With this we recreate last ten entries, sorted by descending timestamp, in the table_posts_summary. At first glance it may appear expensive with so many queries per new post, but consider how often do your authors make new posts? And then consider how many views per second you may have on your summary page?

Finally, when you need to select last 10 posts for each category, you simply select all rows in tbl_posts_summary and present them in your summary page. You can add simple joins on category_id and post_id to fetch category and post titles or other relevant data.

A more concrete example in form of a function. Since this is just an example, it stores limited information per post:

PHP:

Eventually, the code to retrieve last 10 posts per category is very simple. Example given here joins tables to retrieve category name and post title as well:

PHP:

 

There are 0 comments to this post.