Summary of three ways to implement ranking in MySQL without using order by

Assuming business:

View the salary information of the second-ranked employee

Create a database

drop database if exists emps;
create database emps;
use emps;

create table employees(
    empId int primary key, -- employee number gender char(1) NOT NULL, -- employee gender hire_date date NOT NULL -- employee joining date );
create table salaries(
    empId int primary key, 
    salary double -- employee salary);
    
INSERT INTO employees VALUES(10001,'M','1986-06-26');
INSERT INTO employees VALUES(10002,'F','1985-11-21');
INSERT INTO employees VALUES(10003,'M','1986-08-28');
INSERT INTO employees VALUES(10004,'M','1986-12-01');
INSERT INTO salaries VALUES(10001,88958);
INSERT INTO salaries VALUES(10002,72527);
INSERT INTO salaries VALUES(10003,43311);
INSERT INTO salaries VALUES(10004,74057);

Solution

1. (Basic solution)

First find the highest salary in the salaries table, and then use this as a condition to find the second highest salary

The query statement is as follows:

select
	E.empId,E.gender,E.hire_date,S.salary
from
	employees E join salaries S 
on 
	E.empId = S.empId
where	
	S.salary=
	(
    select max(salary)from salaries 
    where 
        salary
        (select max(salary) from salaries)
    );
-- ---------------Query Results------------ --
+-------+--------+------------+--------+
| empId | gender | hire_date | salary |
+-------+--------+------------+--------+
| 10004 | M | 1986-12-01 | 74057 |
+-------+--------+------------+--------+

2. (Self-join query)

First, perform a self-join query on salaries. When s1<=s2 is linked and grouped by s1.salary, the value of count, that is, the number of people with higher salary than him, can be filtered by having to select people with count=2 to get the second highest salary.

The query statement is as follows:

select
	E.empId,E.gender,E.hire_date,S.salary
from
	employees E join salaries S 
on 
	E.empId = S.empId
where S.salary=
	(
    select 
        s1.salary
    from 
        salaries s1 join salaries s2 
    on 
        s1.salary <= s2.salary
    group by 
        s1.salary              
  	having
  	 count(distinct s2.salary) = 2
    );
-- ---------------Query Results------------ --
+-------+--------+------------+--------+
| empId | gender | hire_date | salary |
+-------+--------+------------+--------+
| 10004 | M | 1986-12-01 | 74057 |
+-------+--------+------------+--------+

3. (Self-join query optimization version)

The principle is the same as 2, but the code is much simpler. The above two methods are to introduce the last method. In many cases, group by and order by have their limitations. It is still meaningful for us beginners to master this more practical idea.

select
	E.empId,E.gender,E.hire_date,S.salary
from
	employees E join salaries S 
on
    S.empId =E.empId
where
    (select count(1) from salaries where salary>=S.salary)=2;
-- ---------------Query Results------------ --
+-------+--------+------------+--------+
| empId | gender | hire_date | salary |
+-------+--------+------------+--------+
| 10004 | M | 1986-12-01 | 74057 |
+-------+--------+------------+--------+

This is just a brief summary. If there are any mistakes, please point them out.

Summarize

This concludes this article about three ways to implement ranking in MySQL without using order by. For more relevant content about MySQL ranking without order by, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in: