Query the first N data records of each group

Sample data

Implementation

You can use one of the following methods to query the first N data records of each group:

• Query the row ID of each record and use the WHERE clause to filter the records.

  SELECT * FROM (
    SELECT empno
    , ename
    , sal
    , job
    , ROW_NUMBER() OVER (PARTITION BY job ORDER BY sal) AS rn
    FROM emp
  ) tmp
  WHERE rn < 10;

• Use the SPLIT function.

  For more information, see the last example in MaxCompute learning plan. This method can be used to determine the sequence number of a data record. If the sequence number is greater than the specified number, such as 10, the data records that remain are no longer processed. This improves computing efficiency.