find out the second rank in sql table
Posted by under Sql Server category on | Points: 40 | Views : 2298
create table

CREATE TABLE mgm(name varcha(20),no int,addr varchar(20),dept varchar(20),country varchar(20),salary int)

insert some values in columns
following query used find firs,second,third rank
select name,no,addr,dept,country,salary  from (select dense_rank() over (order by salary desc) as ranking,name,no,addr,dept,country,salary from mgm)a where a.ranking=1

a.ranking=1 //find first rank in table
a.ranking=2= // find second rank in table

Comments or Responses

Posted by: SheoNarayan on: 1/13/2012 Level:HonoraryPlatinum | Status: [Administrator] | Points: 10
Excellent post,

Keep it up!

Posted by: Pkanwar on: 1/25/2012 Level:Starter | Status: [Member] | Points: 10
For finding out second highest salary can use this query:
Select max(Emp_Salary) from Employer
where Emp_Salary not in(Select max(Emp_Salary) from Employer)

Posted by: Kk86kiran on: 2/28/2012 Level:Starter | Status: [Member] | Points: 10
to find n highest salary u can write this simple code

select top 1 salary from (select distinct top n salary from emp order by salary desc) a order by salary

here 'n' means your required number i.e 2nd or 3rd highest salary

Login to post response