CODEWITHSUNDEEP
phpmysql
Ammu
Ammu

Reputation: 138

Select 3rd maximum value from table

I have a table named "salary".

  user_id salary
    1          100
    2          200
    3          500
    4          500
    5          500
    6          700
    7          800
    8          700
    9          800

I need to find out 3rd highest salary from this table.sometimes more than one records wll be in table. How to write query for that? plesae help me

Upvotes: 1

Views: 271

Answers (3)

Alma Do
Alma Do

Reputation: 37365

Use:

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

Upvotes: 1

user1646111
user1646111

Reputation:

This is also an alternative

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

Selecting all records:

select * from salary where salary = (
    SELECT distinct salary 
    FROM salary
    ORDER BY salary DESC
    LIMIT 2, 1
)

Upvotes: 1

peterm
peterm

Reputation: 92785

If you need to get 3d highest salary value which in your case is 500 you can do

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

Output:

| SALARY |
----------
|    500 |

Here is SQLFiddel demo

To get all records where salary equals to 3rd highest

SELECT * 
  FROM salary
 WHERE salary = 
(
  SELECT salary 
    FROM salary
   GROUP BY salary 
   ORDER BY salary DESC
   LIMIT 2, 1
)

Output:

| USER_ID | SALARY |
--------------------
|       3 |    500 |
|       4 |    500 |
|       5 |    500 |

Here is SQLFiddel demo

Upvotes: 4

Related Questions