How to query for second highest salary in EMP in each department? [Resolved]

To find 2nd highest salary

SELECT * FROM Employee E1 WHERE 1 =
(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary < E2.Salary)

Thanks
SagarP
http://www.emanonsolutions.net
http://emanonsolutions.blogspot.com/

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Hi ,
i updated my query check it once again
query for second highest salary in EMP in each department

create table empname
(
name varchar(20),
salary int,
deptno int
)

name salary deptno
inthiyaaz 2000 10
khaja 8000 10
Afroz 2000 20
Heera 1000 30
Naseeb 1000 10
King 1000 20
Imroz 3000 30
Naseer 2000 10
Pankaj 1500 30

with CTE as(select row_number() over(partition by deptno order by salary desc) row,salary,deptno from empname)
select * from CTE where row=2


row salary deptno
2 3000 10
2 1000 20
2 1500 30

Syed Shakeer Hussain

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

second highest salary from emp table for each department

select Max(e1.salary) from tbl_emp e1

where e1.Salary NOT IN(

select Max(e2.salary) from tbl_emp e2

group by e2.departId 

) 

group by e1.departId

----

to get the second last record from any table.
select top 1 e1.* from tbl_emp e1
WHERE e1.empid < (select top 1 e2.empid from tbl_emp e2 order by e2.empid desc)
order by e1.empid desc
--------

ROW_NUMBER()

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition

example:

select ROW_NUMBER() OVER(order by e1.empid desc), e1.* from tbl_emp e1
WHERE e1.empid not in (select distinct top 1 e2.empid from tbl_emp e2 order by e2.empid desc)

result will be

(Row_num) empid empname salary departId
1 5 dgdd 5343 2
2 4 fdgdf 4345 2
3 3 ghfghf 3432 2

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Hi,
check the following link with example to undersatand about 'Row_number ()' in sqlserver2005

http://www.databasejournal.com/features/mssql/article.php/3572301/RowNumber-function-in-SQL-Server-2005.htm

Syed Shakeer Hussain

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Hi,
how to get the second last record from any table.

insert into dupemp(name,salary,deptno) values('1sd',4000,12)

insert into dupemp(name,salary,deptno) values('2sd',4000,12)

insert into dupemp(name,salary,deptno) values('3sd',4000,12)

insert into dupemp(name,salary,deptno) values('6sd',4000,12)

insert into dupemp(name,salary,deptno) values('4sd',4000,12)

with CTE as(select *,ROW_NUMBER() over(order by (select 0))as row from dupemp )
select * from cte where row=(select min(row) from cte where row in(select top 2 row from cte order by row desc))

Outpt:-

Name salary deptno
6sd 4000 12

Syed Shakeer Hussain

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

hai sagar i m saravana can you give explanation to above query plz. (or) send explanation to my mail saravana572@gmail.com

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

to find the second highest salary or nth highest salary, we find many ways, of which:

SELECT * FROM EMPLOYEE WHERE SALARY = (SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY < (SELECT MAX(SALARY) FROM EMPLOYEE))

Explanation: Finding the max salary, then finding the max salary in the rest of the rows. (To find second highest, third highest this fetches easily but.. to find the nth highest it doesn't seem correct way)



SELECT TOP 1 * FROM (SELECT TOP 3 * FROM EMPLOYEE ORDER BY SALARY DESC) AS A ORDER BY SALARY ASC

Explanation: Here, im trying to find out the 3rd highest salary.. First, filter the top n ( n is nth highest) rows with salary order by descending order, then select top 1 from the filtered records.. again ordering the filtered records in ascending order.

Regards,

Awesome Coding !! :)

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator