Geek asks and answers

What is the best way to select the first two records of each group with the SELECT command?

For example, 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 with the SELECT command? If there is no good way to do this, what routine do you suggest? (In PHP)

(model result)

1 1 aaa
2 1 aaa
3 2 aaa
4 2 aaa
6 3 aaa
7 3 aaa

I knew that cross joining a.id> = b.id in a subquery might work, but I'm looking for a more scalable solution that can be applied to a table with millions of records. Thanks

+5
source share
3 answers
select a.*
from Tablename a
where 
(
   select count(*) 
   from Tablename as b
   where a.group = b.group and a.id >= b.id
) <= 2
+8
source

I like this trick that uses the aggregation function GROUP_CONCAT and FIND_IN_SET:

SELECT
  Tablename.*
FROM
  Tablename INNER JOIN (
    SELECT `group`, GROUP_CONCAT(id ORDER BY id) ids
    FROM Tablename
    GROUP BY `group`) grp ON
  Tablename.`group` = grp.`group` AND
  FIND_IN_SET(Tablename.id, ids)<=2
ORDER BY
  Tablename.`group`, Tablename.id

, .

:

SELECT t1.id, t1.`group`, t1.data
from
  Tablename t1 INNER JOIN Tablename t2
  ON t1.`group` = t2.`group` AND t1.id>=t2.id
GROUP BY
  t1.id, t1.`group`, t1.data
HAVING
  COUNT(*)<=2
ORDER BY
  t1.`group`, t1.id, t1.data
+3

, , ,

MSSQL

SELECT TOP 2 * FROM foo;

, Sybase, Oracle , , RDBMS .

MySQL

SELECT * FROM foo LIMIT 2;

Update:

, , . , :)

, RDBMS HAVING . HAVING IN IN.

MSSQL , - ( )

SELECT id, data
    FROM (
        SELECT id, data, Rank() over (Partition BY group ORDER BY id DESC ) AS Rank
        FROM table
        ) rs WHERE Rank <= 2)

RDBMS, , , MSSQL MySQL .

10

topic_id MySQL

-1

More articles:


All Articles