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

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

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

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

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

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us