Top n Large elements in Mysql lookup table

Reprint Annotated Source: http://www.cnblogs.com/liangyongrui/p/8622593.html

Top n Large elements in Mysql lookup table

It's easy to write with a program, and you can use a heap to maintain it, but with SQL?

Solution:

Assuming that the field you want to compare is a, to find the top n rows, the answer is count (a line smaller than a) < n rows. (that's a little bit around.) See an example to understand)

Suppose there's a table like this

Id	Name	Salary
1	Joe	70000
2	Henry	80000
3	Sam	60000
4	Max	90000
5	Janet	69000
6	Randy	85000

Find out the top three salary

SELECT  e1.Name,  e1.SalaryFROM Employee e1WHERE3 >      (        SELECTcount(DISTINCT e2.Salary)        FROM Employee e2        WHERE e2.Salary > e1.Salary      );

Result is

Name	Salary
Joe	70000
Henry	80000
Max	90000

Reference: https://leetcode.com/problems/department-top-three-salaries/solution/

Top n Large elements in Mysql lookup table