Query the first N data records of each group - MaxCompute

This topic describes how to group data records and query the first N data records.

Sample data


empno	ename	job	sal
7369	SMITH	CLERK	800.0
7876	SMITH	CLERK	1100.0
7900	JAMES	CLERK	950.0
7934	MILLER	CLERK	1300.0
7499	ALLEN	SALESMAN	1600.0
7654	MARTIN	SALESMAN	1250.0
7844	TURNER	SALESMAN	1500.0
7521	WARD	SALESMAN	1250.0

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.