What is the best way to select the first two records of each group by a "SELECT" command?
greatest-n-per-group, group-by, mysql, php, sql
Solution
select a.*
from Tablename a
where
(
select count(*)
from Tablename as b
where a.group = b.group and a.id >= b.id
) <= 2
- SQLFiddle Demo
Problem
For instance I have the following table: ``` id group data 1 1 aaa 2 1 aaa 3 2 aaa 4 2 aaa 5 2 aaa 6 3 aaa 7 3 aaa 8 3 aaa ``` What is the best way to select the first two records of each group by a "SELECT" command? If there is no good way to do so, what routine do you suggest?(in PHP) (model outcome) ``` 1 1 aaa 2 1 aaa 3 2 aaa 4 2 aaa 6 3 aaa 7 3 aaa ``` I knew that cross-joining by a.id >= b.id in a sub-query can be working but I am looking for a more scalable solution that can be applied on a table with millions of records. Thanks