Comments on: SQL: selecting top N records per group, another solution

By: Rick James

Rick James — Sun, 17 Apr 2016 01:52:00 +0000

That may be the fastest “Top N” query. I added it to my short collection: http://mysql.rjweb.org/doc.php/groupwise_max

It does have a caveat that may make it not work for some use cases: `Code` must be unique across the entire table.

By: Rick James

Rick James — Thu, 19 Sep 2013 21:10:52 +0000

The GROUP_CONCAT trick has a caveat — there is a default limit of 1024 bytes in the string. (That limit can be changed.)

Another approach is given here:
https://mariadb.com/kb/en/group-by-trick-has-been-optimized-away/
However, for MariaDB, one must do this to keep the optimizer from doing too good a job:
set @@session.optimizer_switch=’derived_merge=off,derived_with_keys=off’;

By: shlomi

shlomi — Thu, 19 Sep 2013 14:27:07 +0000

Well, you could write a sub select per such row. I once compiled some window functions alternatives. Need to pull it from wherever it is

By: shlomi

shlomi — Thu, 19 Sep 2013 14:26:07 +0000

@stephen, not really. As you know mysql does not have window functions. Mimicking the above has no immediate solution.

By: stephen

stephen — Thu, 19 Sep 2013 13:48:31 +0000

is there a way to do a cumulative sum over the window? for example show cumsum of population over each continent sorting countries by name. a more natural application is cumsum over sales rep sorted by time of sale. eg translate into MySQL this Netezza synatx:
select
sum(col1) over
(partition by col2, col3 order by col4 rows between unbounded
preceding and current row) as sum1 from table1 ;

By: Three wishes for a new year | code.openark.org

Three wishes for a new year | code.openark.org — Sun, 16 Sep 2012 04:21:39 +0000

[…] I like GROUP_CONCAT, and the many hacks it provides: [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]. But it makes for a poor substitution to Window Functions, and only solves a subset of […]

By: shlomi

shlomi — Tue, 21 Aug 2012 19:29:03 +0000

@Rick, No, no problem with these integers. See FIND_IN_SET. It's not a LIKE '%157%' expression. It looks for comma delimited values. I personally do not like queries which require me to create new tables and INSERT rows onto them. Such a solution is valid, of course, but I prefer to avoid writing. This allows me better and stricter privileges rules for users, among other issues.

By: Rick James

Rick James — Tue, 21 Aug 2012 17:35:50 +0000

The solution may have trouble with integers…
123,234,1579,…
“157” and “579” will match that, probably incorrectly.

Other approaches are found here:
http://forums.mysql.com/read.php?20,537167,537521
and
http://www.artfulsoftware.com/queries.php

There used to be a way to do the “top N” with nested queries, but MariaDB (at least) has optimized away the “feature” it depended on.

By: shlomi

shlomi — Tue, 21 Aug 2012 07:05:01 +0000

@Øystein,

Thanks – fixed in text. Used a playground database with modified values. After re-importing world.sql population values are now correct.

By: Øystein Grøvlen

Øystein Grøvlen — Tue, 21 Aug 2012 06:57:05 +0000

Very nice, but the population numbers do not look right …