how to select 2nd,3rd or the Nth highest salary from a table in desc or ascen order?

[Rd Dari]

Sir ,

I have some queries for getting the 2nd,3rd and Nth highest salary but these are not working please send me a suitable query.

Thanks!

[Matthew Brown]

What queries have you tried?

[Rd Dari]

SELECT MIN(SALARY) AS Salary FROM user WHERE SALARY IN (SELECT DISTINCT TOP 3 SALARY FROM user ORDER BY SALARY DESC);

OR

SELECT Top 1 Salary AS Salary FROM user WHERE SALARY IN (SELECT DISTINCT TOP 3 SALARY FROM user ORDER BY SALARY DESC) ORDER BY SALARY

I am using MySQL.

[Matthew Brown]

Thinking about it - is there any reason you can't just use SELECT DISTINCT salary FROM user ORDER BY salary DESC and then read the 2nd, 3rd, nth row? Keeps it simple.

If that's no good, the syntax in MySQL to select a fixed number of rows is LIMIT - e.g. SELECT salary FROM users LIMIT 3 - but I'm not sure if that works in subqueries or not. You'd have to try it.

[Rd Dari]

My table is below:

mysql> select *from user;
+--------+--------+
| NAME | SALARY |
+--------+--------+
| Reshma | 10000 |
| Rohit | 25000 |
| Tinku | 27000 |
| Uday | 22000 |
| Jatin | 28000 |
| Jasbir | 20000 |
| Pritam | 35000 |
| Vikas | 32000 |
+--------+--------+
8 rows in set (0.08 sec)
Now I am using this query but there is no right output.
mysql> select salary from user LIMIT 3;
+--------+
| salary |
+--------+
| 10000 |
| 25000 |
| 27000 |
+--------+
3 rows in set (0.00 sec)

mysql>

I wanna my output should be top 3rd salary in descending order is 28000 and in ascending order 22000.

so please help me to find it.

Thanks in advance.

[Matthew Brown]

I didn't mean to just use that query, I meant to use that technique in the queries you've already got!

Though it's still probably going to be simpler to keep the query simple and read the 3rd row, etc.

[Rd Dari]

ok I have done it for 2nd highest but I am not capable for 3rd and Nth position.

my query is ...

SELECT MAX(SALARY) FROM USER WHERE SALARY NOT IN(SELECT MAX(SALARY) FROM USER);

But how can I find 3rd and Nth positions of the salary.....


Thanks!

[Matthew Brown]

Is there a reason you can't use the easy approach I keep mentioning?

[Rd Dari]

yes I have used it order by but there is not working LIMIT 3 in correct order it shows 3 values from table but these are shuffling e.g. 10000,25000,27000.

I want exact 28000. so


I used rownum but this not working in mysql.

[Matthew Brown]

That's not what I meant (although, as I said before, you'd need to use the LIMIT clause in your existing queries, not just copy the example I gave - using LIMIT is the closest MySQL equivalent of using ROWNUM).

The easiest way, I reckon, would be to use SELECT DISTINCT salary FROM user ORDER BY salary DESC. Then read N rows. The Nth row contains your Nth highest value. Can you do it that way?

[Rd Dari]

OK I' ll try for it after some time now I am busy somewhere

my database knowledge is low so I am not comfort for every query.

Thank you for giving me some ideas!

[sriinu reddy]

please try this
mysql> select max(salary) from tablename where salary< (select max(salary) from
tablename where salary<(select max(salary) from tablename ));

[Jayesh A Lalwani]

Sriinu, that is probably the worst way you can do this. Doing max on a huge table is not cheap. Doing it 3 times is even worse. Also, what if he wants the Nth salary, you will have N nested queries? Poor database!

Mathews approach seems to be the best one. Although I think we can do a little better. I believe MySQL allows you to offset the result set, so something like this will work

Select salary from user order by salary desc limit n-1, n


This will give you the nth largest salary

[Deven Detroja]

Below code will Lead you to get N'th largest or Smallest salary values irrespective to Datatype ie. integer or Vearchar.
just change the '<' sign for smaller n'th values.

select * from txt_salary as t1 where 'nth largest' =
(select count(salary) from txt_salary as t2 where t1.salary<=t2.salary)