Does this work for you ?
You can remove the "outer query" from the scratch table to see what
the table contains.
Basically the inner query orders the data by name, score and numbers
them. The outer query simply grabs the first two...
mysql>
mysql> # url: http://forums.mysql.com/read.php?20,275218
mysql>
mysql> select version();
+---------------------+
| version() |
+---------------------+
| 5.0.67-community-nt |
+---------------------+
1 row in set (0.00 sec)
mysql>
mysql> use test;
Database changed
mysql>
mysql> drop table if exists scores;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> create table scores (name varchar(20), score integer);
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> insert into scores values
-> ("Fred",50),
-> ("Fred",30),
-> ("Fred",20),
-> ("Fred",10),
-> ("Fred",5),
-> ("Barney",15),
-> ("Barney",15),
-> ("Barney",10),
-> ("Barney",5 );
Query OK, 9 rows affected (0.00 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from scores;
+--------+-------+
| name | score |
+--------+-------+
| Fred | 50 |
| Fred | 30 |
| Fred | 20 |
| Fred | 10 |
| Fred | 5 |
| Barney | 15 |
| Barney | 15 |
| Barney | 10 |
| Barney | 5 |
+--------+-------+
9 rows in set (0.00 sec)
mysql>
mysql> select `Name`, `The Score` from (
->
-> select
-> name as "Name",
-> score as "The Score",
-> #if(@cnt < 3 OR isNULL(@cnt), score, score) as "The Score",
-> if(name = @last, @cnt := @cnt + 1, @cnt := 1) as "Count",
-> (@last := name) as "Last Record"
->
-> from scores
-> order by name asc, score desc
->
-> ) as scratchtable
-> where `Count` < 3;
+--------+-----------+
| Name | The Score |
+--------+-----------+
| Barney | 15 |
| Barney | 15 |
| Fred | 50 |
| Fred | 30 |
+--------+-----------+
4 rows in set (0.00 sec)
mysql>
mysql> notee
>
Thanks, Jay