1

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

Alma Do
  • 37,009
  • 9
  • 76
  • 105
user1916693
  • 125
  • 2
  • 7
  • 1
    Is this about `first record with 'N-th' salary`, or `all records with N-th salary`? – Alma Do Nov 11 '13 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 Nov 11 '13 at 10:56

3 Answers3

4

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

h2ooooooo
  • 39,111
  • 8
  • 68
  • 102
2

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)
vhadalgi
  • 7,027
  • 6
  • 38
  • 67
1

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

M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • your question has sql tag in it and this code works like a charm in sql-server not to sure about the mysql stuff. – M.Ali Nov 11 '13 at 11:13