2nd Highest record from each group based on Mgr_ID including Manager group (Null group)

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

TableName: EMP_MGR

C#

Emp_ID                 Emp_Name              Salary                      Mgr_ID
1                      Aman                  45000                        NULL
2                      Deepak                35000                           1
3                      Pankaj                32000                           1
4                      Sapna                 25000                           1
5                      Rajan                 50000                        NULL
6                      Nupur                 18000                           5
7                      Anamika               18000                           5
8                      Preet                 22000                        NULL
9                      Shalu                 27000                        NULL
10                    Jyoti                  12000                           9
11                    Omesh                  25000                           9
12                    Rakesh                 21000                           9

C#

SELECT MAX(salary),E1.mgr_id FROM EMP_MGR E1,
(SELECT MAX(salary) AS sal,mgr_id FROM EMP_MGR GROUP BY mgr_id ) E2 
WHERE E1.salary<e2.sal and="" e1.mgr_id="E2.mgr_id"

group="" by="" mode="hold" />
I want to find 2nd Highest record from each group including Managers(NULL group)
Expected Result:

C#

Mgr_ID           Salary
NULL              45000
1                     32000
5                     18000
9                     15000

Posted 20-May-15 20:56pm

Member 11707584

Updated 20-May-15 21:27pm

Thanks7872

v2

Add a Solution

Comments

Thanks7872 21-May-15 3:28am

Query needs to be rectified. Please include that as i didn't get it (at the time of editing) what you meant by that query.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pradip Kumar Sen · Answer 1 · 2015-05-20T21:20:00

Solution 1

Not sure below would be fine for you or not. Just try once...

SQL

;WITH CTE
(
    SELECT salary,mgr_id FROM EMP_MGR GROUP BY mgr_id
)
SELECT MAX(SALARY), mgr_id FROM CTE
WHERE SALARY <> (SELECT MAX(SALARY) FROM CTE)

Posted 20-May-15 21:20pm

Pradip Kumar Sen

willington.d · Answer 2 · 2015-05-25T23:34:00

Solution 2

select * from(select *, rank() over(partition by mgr_id order by mgrid desc) as rno from emp_mgr) as T where rno = 2

Posted 25-May-15 23:34pm

willington.d