How to obtain the first data record by grouping SQL statements

Use the Northwind database

First, query the Employees table.

Query results:

There are only five cities in the city Column

Use ROW_NUMBER () OVER (partition by COL1 order by COL2) to group data first. Note:Group by COL1, sort by COL2 within the group, and the value calculated by this function indicates the sequential number after sorting within each group (continuous and unique in the group ).

The SQL statement is:

Select EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, City, ROW_NUMBER () over (partition by City order by EmployeeID) as new_index
From Employees

Execution result diagram:

It can be seen that the group is City, and the order of the employee ID is.

Select the first record in the output Group

Execute the statement:

Select * from
(Select EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, City, ROW_NUMBER () over (partition by City order by EmployeeID) as new_index
From Employees) a where a. new_index = 1

Execution result diagram: