Obtains the maximum number of rows in a column in all groups.

Obtains the maximum number of rows in a column in all groups.

How to obtain the maximum row of a column in all groups? The following is an example:

There are several employees in the company. Each employee has its own id, group_id (department) and salary (salary ).

Ask employees with the highest salaries in each department of the company

First, identify a problem. Several employees in a department may have the highest salary at the same time, which needs to be listed.

Let's take a look at the employee's database table structure (including only useful columns ):

Field	Type	Null	Key	Default	Extra
Id	Int (11)	NO	PRI	NULL
Group_id	Int (11)	YES		NULL
Salary	Int (11)	YES		NULL

The added test data is as follows:

Id	Group_id	Salary
1	1	100
2	1	200
3	1	200
4	2	200
5	2	300

The steps are as follows:

• 1. Obtain the highest salary of each department
• 1. Find employees whose salaries are equal to the Highest salaries in each department

Obtain the highest salary of each department

select group_id, max(salary) as max_salary from employee group by group_id ;

Result After execution:

Group_id	Max_salary
1	200
2	300

Find employees whose salaries are equal to the Highest salaries in each department

select a.id, a.group_id, a.salary from employee as a, b where a.group_id=b.group_id and a.salary=b.max_salary ;

Assume that the data after the first execution exists in table B.

In this way, the final result is obtained:

Id	Group_id	Salary
2	1	200
3	1	200
5	2	300

We can simply replace B with the code that obtains the highest salary of each department. The combined statement is as follows:

select a.id, a.group_id, a.salary from employee as a, (select group_id, max(salary) as max_salary from employee group by group_id) as b where a.group_id=b.group_id and a.salary=b.max_salary ;

The execution result is the same.

Summary

We first group by Department to obtain the maximum wage (table B) for each group, and then perform the Cartesian Product Between Table a (original table) and Table B, filter out the data we need.

For more articles, visitXiaopengxuan .

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.