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.
[code lang=”sql”]
–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
[/code]
SQL Query for Retrieving the ‘n’ th largest salary from EMPLOYEE table.
[code lang=”sql”]
SELECT salary
FROM Employee EM
WHERE ( @n – 1 ) = ( SELECT COUNT(*)
FROM Employee
WHERE salary > EM.salary)
[/code]
Discover more from Cloud Distilled ~ Nithin Mohan
Subscribe to get the latest posts sent to your email.
[…] 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 […]
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
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