How to obtain the first data record by grouping SQL statements

How to obtain the first data record by grouping SQL statements, using the Northwind database as an Example

How to obtain the first data record by grouping SQL statements, using the Northwind database as an Example

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:

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: