SQL does not use ORDER by to find the second idea

Example: Find current salary (to_date= ' 9999-01-01 ') ranked second most employee number emp_no, salary salary, last_name, and first_name, no ORDER BY
CREATE TABLE ' Employees ' (
' Emp_no ' int (one) is not NULL,
' Birth_date ' date not NULL,
' first_name ' varchar (not NULL),
' last_name ' varchar (+) not NULL,
' Gender ' char (1) Not NULL,
' Hire_date ' date not NULL,
PRIMARY KEY (' emp_no '));
CREATE TABLE ' salaries ' (
' Emp_no ' int (one) is not NULL,
' Salary ' int (one) is not NULL,
' From_date ' date not NULL,
' To_date ' date not NULL,
PRIMARY KEY (' emp_no ', ' from_date '));

Solution Ideas:

The method of this problem is more

Method One:

Using two times Max, together with select nesting

The main idea is to combine the multi-layer select nesting with the max () function 1, first using the Max () function to find the highest current salary in salaries, that is, select MAX (Salary) from salaries WHERE to_date = ' 9999-01-01 ' 2, and then use inner join to connect employees and salaries table, the qualification is "the same employee" e.emp_no = s.emp_no, "current" s.to_date = ' 9999-01-01 ' and "non-salary highest" s.salary Not IN (SELECT MAX (salary) from salaries WHERE to_date = ' 9999-01-01 ') 3, to find the highest salary under the above restrictions, that is, for all employees of the sub-high salary SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name  FROM employees AS e INNER JOIN salaries AS s  ON e.emp_no = s.emp_no WHERE s.to_date =  ‘9999-01-01‘ AND s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date =  ‘9999-01-01‘ )

Method Two:

Use Max to select the largest, use where to limit the maximum value, and then take the largest one

Select A.emp_no,max (b.salary), A.last_name,a.first_name

From employees a INNER join salaries B

On A.emp_no=b.emp_no

Where b.salary< (select Max (salary) from salaries)

and b.to_date = ' 9999-01-01 '

SQL does not use ORDER by to find the second idea