how to retrieve second max salary without using subquery and limit in mysql?

[suresh64633]

Hi Guys

 

How do i get 2nd highest values(say salary) without using Sub-query or Limit?

 

Many Thanks.

[corbin]

Without limit, it's not possible unless you don't mind pulling all of the results.

 

 

Why can't you use limit?

[suresh64633]

Hi guys

I got the query

# OPTION ONE: To find 3rd highest

select * from salary e where
3 =(select count(distinct salary) from salary where e.salary<=salary)

 

 

# OPTION TWO: It find only 2nd highest.

SELECT max( p1.sal ) FROM jos_salary p1, jos_salary p2 WHERE p1.sal < p2.sal

[Daniel0]

SELECT sal FROM jos_salary ORDER BY sal DESC LIMIT 1,1;

 

Order descendingly, limit to 1 and offset by 1. I believe that should be the second highest.

[Mchl]

SELECT max( p1.sal ) FROM jos_salary p1, jos_salary p2 WHERE p1.sal < p2.sal

 

I like this one! 2nd highest value without using Sub-query or Limit

[Daniel0]

Though of course it'll be less efficient than just using a limit.

[Mchl]

Of course. But interesting as a puzzle.