find out the second rank in sql table

Posted by Gow.net under Sql Server category on 1/13/2012 | Points: 40 | Views : 2606

Post Code |

Search Codes |

Code Home

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

Alert Moderator

Bookmark It

Comments or Responses

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

Excellent post Gow.net,

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

Latest Code Snipptes