Mysql retriving the nth record

Question

Suppose I have a table named EMPLOYEE containing the following attributes

 (EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, JOB_ID, MANAGER_ID, Salary)

Can I Display the Nth highest salary drawing employee details Please help

Is this about first record with 'N-th' salary, or all records with N-th salary? — Alma Do
– Alma Do, Commented Nov 11, 2013 at 10:52
Suppose the salary column contains value (6700,6400,6000,5400,3200,1200) in descending order .I would like the details containing salary 6000 ie the 3rd highest salary . — user1916693
– user1916693, Commented Nov 11, 2013 at 10:56

h2ooooooo · Accepted Answer · 2013-11-11 10:55:58Z

ORDER BY and LIMIT where 10 is n + 1:

SELECT * FROM employees ORDER BY Salary DESC LIMIT 10, 1

(If you want the first record, use LIMIT 0, 1. For the tenth, use LIMIT 9, 1 etc.)

vhadalgi · Accepted Answer · 2013-11-11 11:01:48Z

try this

put n > 1 to get corresponding results

n=3 must give you second highest salary

 SELECT * --This is the outer query part FROM Employee Emp1 WHERE (N-1) = ( /* Subquery starts here */ SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary)

M.Ali · Accepted Answer · 2013-11-11 11:01:40Z

Test Table

CREATE TABLE Test (ID INT IDENTITY(1,1), Salary INT) INSERT INTO Test VALUES (100), (200), (300), (400), (500) SELECT * FROM Test

Query

SELECT TOP 1 Salary FROM (SELECT TOP 3 Salary FROM Test ORDER BY Salary DESC)q ORDER BY Salary ASC

In your Sub-query SELECT TOP Nth the rest remains the same and it will get you the desired results

your question has sql tag in it and this code works like a charm in sql-server not to sure about the mysql stuff.

Collectives™ on Stack Overflow

Mysql retriving the nth record

3 Answers 3

Comments

Comments

1 Comment

Hot Network Questions