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.
Sql
</p>
<p>--Creating Employee Table<br />
--drop table Employee<br />
CREATE TABLE Employee (EmpId BIGINT IDENTITY(10000,1) PRIMARY KEY, Salary MONEY)<br />
go</p>
<p>-- Inserting dummy data - 2000 records are inserting with variable salary<br />
DECLARE @counter INT<br />
DECLARE @multiplier int<br />
SET @counter = 10000<br />
@multiplier = 33;</p>
<p>WHILE(@counter < 12000)<br />
BEGIN</p>
<p> INSERT INTO Employee(Salary) VALUES (@counter + (@counter - 1232 - 3232))<br />
-- just a trick of inserting some salary, diff for each employee.</p>
<p> SET @counter = @counter +1<br />
END</p>
<p>SQL Query for Retrieving the ‘n’ th largest salary from EMPLOYEE table.
Sql
</p>
<p>SELECT salary<br />
FROM Employee EM<br />
WHERE ( @n - 1 ) = ( SELECT COUNT(*)<br />
FROM Employee<br />
WHERE salary > EM.salary)</p>
<p>Discover more from C4: Container, Code, Cloud & Context
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