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 |
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.