Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

The challenge is to find the employees with the second highest salary in each department. However, it is a little more complicated because if two employees have the same salary, you need to list both of them.

TSQL Beginners Challenge 1 - Find the second highest salary for each department

Solution

Dec 16 2011 10:06PM by Ravi Tuvar   

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

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


Data in table
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 

Tags: sql,department wise salary


Ravi Tuvar
611 · 0% · 58
2



Submit

2  Comments  

  • nice bhai

    commented on Dec 16 2011 11:16PM
    ashishmalwal
    2213 · 0% · 6
  • will not work :) Row number function enumerates regardless data are even (here we have such example in the test data)

    commented on Jan 11 2012 6:21AM
    damar123
    2755 · 0% · 4

Your Comment


Sign Up or Login to post a comment.

"Solution" rated 5 out of 5 by 2 readers
Solution , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]