How to achieve sorting and getting the first record in a group in DB2?

9 months ago

Jackson Davis

1 minute

To group, sort, and select the first record of each group in DB2, you can use the following SQL statement:

WITH ranked_data AS (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY group_column ORDER BY sort_column) AS rn
  FROM your_table
)
SELECT * 
FROM ranked_data
WHERE rn = 1;

In this SQL statement, the ROW_NUMBER() window function is first used to sort each group of data and assign a row number to each row. Then, the PARTITION BY group_column is used to specify the grouping column, and the ORDER BY sort_column is used to specify the sorting column. Finally, by filtering the data with row number equal to 1 in the outermost query, we can obtain the first data of each group.