Getting the first 5 records of type X in MySQL
I just got a question from Dirk Bonenkamp. He has a query which returns a number of contacts, but he wants to get no more than 5 contacts of per customer. After thinking really hard, I came up with a solution using variables and a subquery.
To test this idea, I used the ‘world’ database available at MySQL dev zone. This query selects no more than 5 countries per continent.
SET @a=0; SET @b=""; SELECT Name, Continent FROM (SELECT @a:=IF(@b=Continent, @a+1, 0) AS a, Name, @b:=Continent AS Continent FROM Country ORDER BY Continent) AS t WHERE a < 5;
This isn’t one of the nicest looking solutions, but it works. Do you have a better solution? Please let me know 
.
09 Nov 2007 Arnold Daniels
This seems to be a common task. More solutions:
http://jan.kneschke.de/projects/mysql/groupwise-max
How does that query scale? The world database is tiny.
Here’s an example query to return the top three cities per state by population. I presented this at OSCON 2006 in a talk “SQL Outer Joins for Fun and Profit”.
SELECT c.state, c.city_name, c.population
FROM cities AS c
LEFT JOIN cities AS c2 ON c.state = c2.state
AND c.population <= c2.population
GROUP BY c.state, c.city_name, c.population
HAVING COUNT(*) <= 3
ORDER BY c.state, c.population DESC;
Hi Arnold Daniels.
It’s very cool solution, thanks for share!
Marc: Yes I’ve read your solutions some time ago. Though nice, that only works (well) for a single record per type.
Bill: That looks like a nice solution. You do get a huge resultset which has to be filtered with a HAVING, so I’m not sure if it scales better.
Jeff: I don’t know how it scales. I might run some benchmarks this weekend (if I manage to stay away from hangovers). If you want to benchmark it, please do.
I’m don’t have access to a MySQL database so can’t try this, but how about using a LIMIT 5 clause?
define “first 5″
your solution looks like it’s actually “any 5″
there are several ways to do it, depending on what you really mean
Nice! You probably already know this — on your subquery, add ‘, Country’ after the order by statement if you wish to have the countries in alphanumerical order…
SELECT @a:=IF(@b=Continent, @a+1, 0) AS a, Name, @b:=Continent AS Continent FROM Country ORDER BY Continent, Country
If the query does not scale well on a large database and you can control the output (limiting the number of countries), try this:
SELECT Name, GROUP_CONCAT(DISTINCT Name order by Name ASC SEPARATOR ‘, ‘) AS Countries FROM Country GROUP BY continent;
Note that it cannot limit the number of countries per line. Perhaps this can be a feature in a future version of mySql?
-David
The most elegant solution is use top-n queries.
Oh wait, I forgot, this piece of crap dbms doesn’t know about top-n queries, among other things missing like data integrity features, minus operation, real trigger support (without memory leaks), stored procedures that are using the query cache….etc.
On short, the solution is to use PostgreSQL.
Thank your for removing my comment.
That remembers me about the censorship.
Nice and paranoid are we gigiduru. I have to approve comments manually and at 00:43 I’m either sleeping or drinking beer.
I understand the frustration when you have the superior product, but can’t get people to switch. We supper the same with Javeline PlatForm.
Sorry for stepping on your toes but I really hate when someone disregard my comments for “unknown” reasons or just because he/she feels so. It’s rude. Now that we cleared out why it happened, I guess there’s no reason to beat the drum on it anymore.
Paranoid – yeah, maybe, I’ll take it as a compliment. Especially when mysql crashes doing inserts on a 400 GB database using myisam tables… Not my storage engine of choice either.
Also, keep in mind, I started down on mysql road with all the enthusiasm, which also happened to lose it bit by bit dealing with its crapness on daily basis.