SQL Query for ‘n’ th highest salary of employee table

IGate Recruitment, in written test has asked about to write an sql query for finding ‘n’ th highest salary from empolyee table. Here is a quick solution

Query for Creating Test Table and inserting dummy data.

SQL15 lines
--Creating Employee Table
--drop table Employee
CREATE TABLE Employee (EmpId BIGINT IDENTITY(10000,1) PRIMARY KEY, Salary MONEY)
go
-- Inserting dummy data - 2000 records are inserting with variable salary
DECLARE @counter INT
DECLARE @multiplier int
SET @counter = 10000
@multiplier = 33;
WHILE(@counter < 12000)
BEGIN
 INSERT INTO Employee(Salary) VALUES (@counter + (@counter - 1232 - 3232))
 -- just a trick of inserting some salary, diff for each employee.
 SET @counter = @counter +1
END

SQL Query for Retrieving the ‘n’ th largest salary from EMPLOYEE table.

SQL5 lines
SELECT  salary
FROM    Employee EM
WHERE   ( @n - 1 ) = ( SELECT   COUNT(*)
                       FROM     Employee
                       WHERE    salary > EM.salary)

Discover more from C4: Container, Code, Cloud & Context

Subscribe to get the latest posts sent to your email.

Written by

Nithin Mohan TK

Hello Namaste! 👋🏻 I'm an accomplished Enterprise Solution Architect & Engineering Leader with 20+ years of progressive experience architecting, building, and scaling transformative enterprise solutions. ⚜️ Solution Architect / AI & Cloud 📌 Galway, Ireland

3 comments

  1. […] This post was mentioned on Twitter by Nithin Mohan T K, Nithin Mohan T K. Nithin Mohan T K said: Query for ‘n’ th highest salary of employee table: IGate in writter test has asked about to write an sql query for… http://bit.ly/cGaJHY […]

  2. hello sir

    i have col sal_salary in cms database
    12000
    22000
    16000
    8000
    12000
    5000
    25000
    12500
    total column in sal_salary 400
    i want to sum 200 to 300 column

  3. plz sir help me about this query

    (2) i have column in ip_code 10 digit
    11-333-45678
    11-786-76567
    11-876-12345
    11-987-07543
    11-567-87654
    output———————————————
    333-46
    786-45
    876-12
    means 3 between 8 no come in full column
    plz sir find solution in this question
    thankyou sir

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.