|
View previous topic :: View next topic
|
| Author |
Message |
rahulbose
New User
Joined: 21 Feb 2005
Posts: 3
|
|
|
|
| How can i find second max salary from a table |
|
| Back to top |
|
 |
mcmillan
Site Admin
Joined: 18 May 2003
Posts: 1210
Location: India
|
|
|
|
Dear Rahul,
I think this is answered before. Anyway:
The query to find the SECOND MAXIMUM salary:
| Quote: |
| SELECT MAX(SALARY) FROM EMPTABLE WHERE SALARY < (SELECT MAX(SALARY) FROM EMPTABLE) ; |
To find the SECOND MINIMUM salary:
| Quote: |
| SELECT MIN(SALARY) FROM EMPTABLE WHERE SALARY > (SELECT MIN(SALARY) FROM EMPTABLE) ; |
To find the Nth MAXIMUM SALARY of the table:
| Quote: |
| SELECT SALARY FROM EMPTABLE A WHERE (N-1) IN (SELECT COUNT(*) FROM EMPTABLE B WHERE A.SALARY > B. SALARY) |
|
|
| Back to top |
|
|
ashok_bln
Warnings : 1
New User
Joined: 19 Feb 2005
Posts: 5
Location: bangalore
|
|
|
|
i think this will help u
select max(sal) from table1 where sal<(select max(sal) from table1)
table1 is table name
sal is salary cloumn name
regards
ashok |
|
| Back to top |
|
|
mcmillan
Site Admin
Joined: 18 May 2003
Posts: 1210
Location: India
|
|
|
|
Dear Ashok,
Is there any difference from the query posted above? |
|
| Back to top |
|
|
radhakrishnan82
Active User
Joined: 31 Mar 2005
Posts: 435
Location: chennai, India
|
|
|
|
You can also find the distinct second maximum salary from the employee table:
SELECT DISTINCT (SALARY)
FROM EMPTABLE A
WHERE 2 = (SELECT COUNT (DISTINCT (B.SALARY)) FROM EMPTABLE B
WHERE A.SALARY <= B.SALARY); |
|
| Back to top |
|
|
chandramouli.v
New User
Joined: 21 Mar 2005
Posts: 12
|
|
|
|
SELECT * FROM <tablename> a
WHERE N = (SELECT count(DISTINCT(b.col1))
FROM <tablename> b WHERE a.col1<=b.col1)
N is the nth highest value
mention the value |
|
| Back to top |
|
|
karuna
New User
Joined: 03 May 2005
Posts: 10
Location: bangalore
|
|
|
|
Hi Mcmillan,
m not getting the query of nth maximum salary which you have given below. Could you please explain the query?
| mcmillan wrote: |
Dear Rahul,
I think this is answered before. Anyway:
The query to find the SECOND MAXIMUM salary:
| Quote: |
| SELECT MAX(SALARY) FROM EMPTABLE WHERE SALARY < (SELECT MAX(SALARY) FROM EMPTABLE) ; |
To find the SECOND MINIMUM salary:
| Quote: |
| SELECT MIN(SALARY) FROM EMPTABLE WHERE SALARY > (SELECT MIN(SALARY) FROM EMPTABLE) ; |
To find the Nth MAXIMUM SALARY of the table:
| Quote: |
| SELECT SALARY FROM EMPTABLE A WHERE (N-1) IN (SELECT COUNT(*) FROM EMPTABLE B WHERE A.SALARY > B. SALARY) |
|
|
|
| Back to top |
|
|
radhakrishnan82
Active User
Joined: 31 Mar 2005
Posts: 435
Location: chennai, India
|
|
|
|
| What does the error message says while querying? |
|
| Back to top |
|
|
ajay_dheepak
New User
Joined: 12 May 2005
Posts: 32
Location: Chennai
|
|
|
|
Quote:
SELECT SALARY FROM EMPTABLE A WHERE (N-1) IN (SELECT COUNT(*) FROM EMPTABLE B WHERE A.SALARY > B. SALARY)
It didnt give any error message. Just explain the query |
|
| Back to top |
|
|
karuna
New User
Joined: 03 May 2005
Posts: 10
Location: bangalore
|
|
|
|
| radhakrishnan82 wrote: |
| What does the error message says while querying? |
Hi,
Not getting any error message but m not getting the query.So please explain it. |
|
| Back to top |
|
|
paru
New User
Joined: 24 Jun 2005
Posts: 9
Location: INDIA
|
|
|
|
Hi karuna,
Let me explain the query with an example.Suppose you want find the third highest salary.In this case N=3.
The sub Query SELECT COUNT(*) FROM EMPTABLE B WHERE A.SALARY > B. SALARY will be 2 only when there are two values greater than the answer you want.The condition,
SELECT SALARY FROM EMPTABLE A WHERE 2 IN 2, will then become satisfied and you will be getting the third maximum salary.
Hope this makes sense.
Cheers,
Paru  |
|
| Back to top |
|
|
|
|
