Finding the Second Maximum Value for each group

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Finding the Second Maximum Value for each group

Author

Topic

jkmarvelmani
Starting Member

8 Posts

Posted - 2008-10-07 : 15:16:20

I have a table with EmpName and Salaray. The EmpName column is not unique so that same name may occur with different salaray. I need to find the second Maximum value for each employee.How to write the query with group by option.I tried but i get only one value as output. Anybody help me ...Thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-07 : 15:24:48

What makes the row unique? Do you have an identity column or employee number in the table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jkmarvelmani
Starting Member

8 Posts

Posted - 2008-10-07 : 15:27:51

I have another one column Month and Year which makes the column unique.
But in the result i don't need that column to be displayed

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-07 : 15:31:27

SELECT Empname,MAX(SALARY) FROM yourtable WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM yourtable)
group by Empname

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-07 : 15:39:37

sodeep, your solution could return inaccurate results.

jkmarvelmani, month and year don't make sense for an employee table. Could you post all of your columns so that we can take a look?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jkmarvelmani
Starting Member

8 Posts

Posted - 2008-10-07 : 15:49:04

Its works for me. Thank you guys for your help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-07 : 16:02:31

Perhaps it works right now, however it will not work in the future when you've got someone else with a max salary that matches someone's second max salary.

You should not use a solution that is broken.

Is this a homework assignment or will this code go into production?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 00:55:10

Seems like what you want is this

DECLARE @EMPSal table
(EmpName varchar(20),
[Month] int,
Salary money)

insert into @EmpSal
SELECT 'Ram',3,15000.00 union all
SELECT 'Sita',3,10000.00 union all
SELECT 'Shankar',3,12500.00 union all
SELECT 'Ram',5,17000.00 union all
SELECT 'Shankar',8,15000.00 union all
SELECT 'Sita',7,15000.00 union all
SELECT 'Ram',12,19000.00 




SELECT t1.EmpName,MAX(t1.Salary) AS SecMaxSal
FROM @EmpSal t1
LEFT JOIN (SELECT EmpName,MAX(Salary) AS MaxSal
           FROM @EmpSal
           GROUP BY EmpName) t2
ON t2.EmpName=t1.EmpName
AND t2.MaxSal=t1.Salary
WHERE t2.EmpName IS NULL
GROUP BY t1.EmpName


output
---------------------------
EmpName              SecMaxSal
-------------------- ---------------------
Ram                  17000.00
Shankar              12500.00
Sita                 10000.00

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources