Comments on: SQL: selecting top N records per group

By: Limit each group in group by | BlogoSfera

Limit each group in group by | BlogoSfera — Fri, 28 Jun 2013 21:02:37 +0000

[…] think one of these solutions could work, but not how: https://shlomi-noach.github.io/blog/mysql/sql-selecting-top-n-records-per-group […]

By: shlomi

shlomi — Thu, 16 May 2013 05:14:38 +0000

@Erich,

Yes, the use of commas assume no commas in values; I also sometimes use char(0) or char(9) which nobody uses within texts

By: Erich

Erich — Tue, 14 May 2013 09:19:07 +0000

this is just what i needed – i suggest using char(1) rather the ‘,’ tho

By: MySQL/QueryScript use case: DELETE all but top N records per group | code.openark.org

Thu, 09 Feb 2012 08:33:17 +0000

[…] SQL: selecting top N records per group […]

By: shlomi

shlomi — Thu, 15 Dec 2011 16:55:42 +0000

@Keith,
Yes, that’s possible; there’s a balance one would want to maintain. It’s easy to store 1000 rows in a table, and is faster (verify) than doing 3 joins of 10 rows. Storing 100000 is not as efficient, and joins are required.

By: Keith

Keith — Thu, 15 Dec 2011 14:40:39 +0000

Useful idea.

One minor suggestion would be to use a table with the numbers 0 to 9 which can then be repeatedly cross joined against itself to produce as big an integer as required.

Maybe a touch less efficient but more flexible and can then easily be used on several different queries.

By: shlomi

shlomi — Mon, 10 Jan 2011 04:03:53 +0000

@januzi,
I’m not sure what it is you’re trying to avoid or what your situation is.
The query *requests* that you scan the entire table. It says: “for all rows in the table, group them and sort them within the groups”.
It’s true that eventually we then only use top n rows per group. If your groups are small, this makes little difference. If your groups are large, it does.

By: januzi

januzi — Sun, 09 Jan 2011 16:45:14 +0000

So, the most reasonable solution is to save the query result in the cache (secondary table, memcached, file, etc). I’ll check that last query. Maybe it will give me less than 80k+ Rows_examined (as mysql+percona log patch says in the slow query log).

By: shlomi

shlomi — Sun, 09 Jan 2011 06:47:00 +0000

We could do a contest: who gets as many "Extra" comments in a single query :) There's no avoiding the "Using join buffer"m as we're essentially joining the tinyint_asc table to Country without an index. Here's yet another possible execution plan for the above query.

+----+-------------+-------------+-------+---------------+---------+---------+------+------+---------------------------------------------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows | Extra                                       |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+---------------------------------------------+
|  1 | SIMPLE      | Country     | ALL   | NULL          | NULL    | NULL    | NULL |  239 | Using temporary; Using filesort             |
|  1 | SIMPLE      | tinyint_asc | range | PRIMARY       | PRIMARY | 1       | NULL |    4 | Using where; Using index; Using join buffer |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+---------------------------------------------+

This may soften the previous impression. We only iterate the Country table once. For each row we join 4 rows from tinyint_asc using join buffer. Since we're grouping by columns from two distinct tables, we can't utilize an index to work out the GROUP + ORDER BY.

By: januzi

januzi — Sun, 09 Jan 2011 00:44:12 +0000

Nice combo in the “extra” column.