SQL statements used to retrieve the first N records for DB2 Classification

The SQL statements described below are used to retrieve the first N records of the DB2 classification. If you have encountered the problem of retrieving the first N records of the DB2 classification, take a look.

There is a table t1 with the following structure:
Sort Category numbers
A aa 1
A aa2 4
A aa3 6
B bb 1
B bb2 3
B bb3 5
C cc 1
C cc2 5
C cc3 7
The results to be queried are the first two records of each category, sorted by order. The results are as follows:
Sort Category numbers
A aa 1
A aa2 4
B bb 1
B bb2 3
C cc 1
C cc2 5

Create table t1 (
YTypeCode varchar (10 ),
YMemo varchar (10 ),
YOrder int
)

Insert into t1 (yTypeCode, yMemo, yOrder) values ('A', 'A', 1 );
Insert into t1 (yTypeCode, yMemo, yOrder) values ('A', 'aa2 ', 4 );
Insert into t1 (yTypeCode, yMemo, yOrder) values ('A', 'aa3', 6 );
Insert into t1 (yTypeCode, yMemo, yOrder) values ('B', 'bb', 1 );
Insert into t1 (yTypeCode, yMemo, yOrder) values ('B', 'bb2', 2 );
Insert into t1 (yTypeCode, yMemo, yOrder) values ('B', 'bb3', 3 );
Insert into t1 (yTypeCode, yMemo, yOrder) values ('C', 'cc', 1 );
Insert into t1 (yTypeCode, yMemo, yOrder) values ('C', 'cc2', 5 );
Insert into t1 (yTypeCode, yMemo, yOrder) values ('C', 'cc3', 8)

Implementation;

SELECT * FROM t1 t
WHERE (SELECT count (*) FROM t1 WHERE yTypeCode = t. yTypeCode AND yOrder <t. yOrder) <2
 

Usage of DB2 Merge statements

Overview of DB2 Flow Control clauses

Principle of DB2 cursor

Two ways to store db2 logs

Common DB2 cycle usage