Find nth Highest Salary from Employee Table

How to Find 2nd ,3rd or upto....Nth highest Salary of any employee in SQl

Step 1 Create Table
Create Table emp
(
empName nvarchar(max),
Salary int
)
Step 2 Insert records

1 ) Insert into values emp ('John',2000)
2 ) Insert into values emp ('Kim',5000)
3 ) Insert into values emp ('Walter',1500)
4 ) Insert into values emp ('Obama',3000)
5 ) Insert into values emp ('John Vincet',2500)
6 ) Insert into values emp ('Womek',3500)


Step 3 Query Find the 2nd Highest Salary from emp Table

Select * from emp a Where (2=(Select Count(Distinct b.Salary) from emp b Where b.Salary >= a.Salary))

Step 4 Execute the Step 3 Query


Step 5 For find the nth Highest Salary from emp Table
Select * from emp a Where (n=(Select Distinct(b.Salary) from emp b Where b.Salary >= a.Salary))

Step 6 Check the Ouptut..

Comments

  1. UnknownAugust 20, 2010 at 3:42 AM

    Select * from emp a Where (2=(Select Count(Distinct b.Salary) from emp b Where b.Salary > a.Salary))


    GIVES wrong result.
    It displays Third Highest Salary
    EX. if in SubQuery u written where( 2=(select...)) Then returns 3rd Highest Salary

    where( 1=(select...)) Then returns 2rd Highest Salary

    where( 3=(select...)) Then returns 4rd Highest Salary

    SO U have To modify UR Query to
    Select * from emp1 a Where (0=(Select Count(Distinct b.Salary) from emp1 b Where b.Salary >= a.Salary))

    ReplyDelete
  2. shubhank upadhyayAugust 20, 2010 at 5:46 AM

    Dear Mahitnahi (BJ)

    Query is giving right answer !

    May be Possible u did mistake while copy paste the code.
    I think u forgot to apply '=' sign in following
    conditions
    Where b.Salary >= a.Salary)

    Check it and Let me Know

    Thanks
    Shubhank Upadhyay

    ReplyDelete

Post a Comment

Popular posts from this blog

String Function in SQL-Substring,Reverse,CharIndex

Suppose You have String "~/ABC/PQR.xxx" in your tables Now u Want to string PQR.xxx fromm above string So For That Follow the Steps 1) Open NewQuery Window 2) Paste following Code declare @str as varchar(200),@str1 as varchar(200),@rev as varchar(200) set @str ='~/ABC/PQR.xxx' select @rev = reverse(@str) SELECT @str1 = CHARINDEX('/', reverse(@str)) select @str1,@rev'@rev',reverse(substring(@rev,0,convert(int,@str1))) 3) Execute the Code 4) You will get the Output Like that PQR.xxx
Read more

Difference Between ArrayList and Generic.List C#/VB.Net

Hi, Last few Days, I learnt a very basic and important thing about  that ,When one should go for ArrayList or Generic.List in coding......On basis of that, I found  few difference between both. ArrayList Its comes under System.Collections namespace. Its contains Array of Object.i.e Any objects type we can store in a ArrayList.We can store any no. of objects at runtime. ArrayList aryList= new ArrayList(); // No Need to specify the object type,can store anything arList.Add(1); arList.Add("Shubhank"); arList.Add("False"); We can store any objects in ArrayList and can retrieve only Objects from ArrayList... We can say that ArrayList is not Type-Safe .At the Time of inserting recods ,we can store any type of objects,but at the time of fetching we can't be sure that  Values  which are populate by arList (ArrayList object ) of what type..however.. we have to following code while fetching records/items from Array list foreach(Object o in arList) { ....
Read more

How to Use Not IN operator

Hello Friends Here i am explaining an example of NOT IN operator. Suppose you have two tables one is employee and Bademployee employee and Bademployee has relationship. Bademployee contains records who are bad Now you want to show the Good employee so following is the example Step : 1 create Table emp Run this Query in your Query Analyzer for table emp 1) CREATE TABLE [dbo].[emp]( [empId] [int] NOT NULL, [empName] [nvarchar](max) NULL, CONSTRAINT [PK_emp] PRIMARY KEY CLUSTERED ( [empId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Now Run this Query in your Query Analyzer for table bademp CREATE TABLE [dbo].[bademp]( [empid] [int] NOT NULL, [bademp] [nvarchar](50) NOT NULL, CONSTRAINT [PK_bademp] PRIMARY KEY CLUSTERED ( [empid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY
Read more