MySQL: Select top n max values?

If you do:

select *from torder by value desclimit 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 twhere t.value >= ANY (select value from t order by value limit N)

Use the following SQL query.

SELECT salary FROM salesperson ORDER BY salary DESCLIMIT 2,1

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 ) S1JOIN 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