Mysql: Select top N max values?

问题:

I am really confused about the query that needing to return top N rows having biggest values on partircular collumn.

For example, if the rows N-1, N, N + 1 have same values. Must I return just top N or top N + 1 rows.

Thank you very much.

回答1:

If you do:

select *
from t
order by value desc
limit N

You will get the top N rows.

If you do:

select *
from t join
     (select min(value) as cutoff
      from (select value
            from t
            order by value
            limit N
           ) tlim
    ) tlim
    on t.value >= tlim;

Or you could phrase this a bit more simply as:

select *
from t join
     (select value
      from t
      order by value
      limit N
    ) tlim
    on t.value = tlim.value;

The following is conceptually what you want to do, but it might not work in MySQL:

select *
from t
where t.value >= ANY (select value from t order by value limit N)

回答2:

You should use self join for this.

1. first find the top (n) possible values for a perticular column
2. join it with same table based on the primary key

For E.g. on below sample table

CREATE TABLE `employee` (
  `ID` INT(11)   AUTO_INCREMENT PRIMARY KEY,
  `NAME` VARCHAR(50) NOT NULL,
   `SALARY` INT(11) NOT NULL , 
    JOINING_DATE TIMESTAMP  
) ENGINE=MYISAM 

INSERT INTO  employee (NAME,salary,joining_date)    VALUES('JAMES',50000,'2010-02-02'),
('GARGI',60000,'2010-02-02'),('DAN',30000,'2010-02-02'),('JOHN',10000,'2010-02-02'),('MICHEL',70000,'2010-02-02'),
('STIEVE',50000,'2010-02-02'),('CALRK',20000,'2010-02-02'),('BINNY',50000,'2010-02-02'),('SMITH',40000,'2010-02-02'),
('ROBIN',60000,'2010-02-02'),('CRIS',80000,'2010-02-02');

With the above table-data set up Query to find employees having top 3 salaries would be :

SELECT e1.* FROM 
(SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 3 ) S1
JOIN employee  e1 
ON e1.salary = s1.salary 
ORDER BY e1.salary DESC 

TIP:-

If you need top 4 then just change LIMIT 3 to LIMIT 4

回答3:

Use the following SQL query.

SELECT salary FROM salesperson 
ORDER BY salary DESC
LIMIT 2,1