This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes JDBC and the fly likes how to select 2nd,3rd or the Nth highest salary from a table in desc or ascen order? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to select 2nd,3rd or the Nth highest salary from a table in desc or ascen order?" Watch "how to select 2nd,3rd or the Nth highest salary from a table in desc or ascen order?" New topic
Author

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

Rd Dari
Ranch Hand

Joined: Feb 22, 2010
Posts: 206

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
Bartender

Joined: Apr 06, 2010
Posts: 4376
    
    8

What queries have you tried?
Rd Dari
Ranch Hand

Joined: Feb 22, 2010
Posts: 206

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
Bartender

Joined: Apr 06, 2010
Posts: 4376
    
    8

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
Ranch Hand

Joined: Feb 22, 2010
Posts: 206

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
Bartender

Joined: Apr 06, 2010
Posts: 4376
    
    8

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
Ranch Hand

Joined: Feb 22, 2010
Posts: 206

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

my query is ...




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


Thanks!
Matthew Brown
Bartender

Joined: Apr 06, 2010
Posts: 4376
    
    8

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

Joined: Feb 22, 2010
Posts: 206

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
Bartender

Joined: Apr 06, 2010
Posts: 4376
    
    8

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
Ranch Hand

Joined: Feb 22, 2010
Posts: 206

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
Greenhorn

Joined: Mar 10, 2009
Posts: 11
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
Bartender

Joined: Jan 17, 2008
Posts: 2347
    
  28

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
Greenhorn

Joined: Mar 11, 2013
Posts: 1

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.





Reduce, Reuse & Recycle Paper - Save Earth
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to select 2nd,3rd or the Nth highest salary from a table in desc or ascen order?