DECLARE @test TABLE(Empnm VARCHAR(10), Salary VARCHAR(10)) INSERT INTO @

Please get the 4 th maximum salary from a table without
using any sql keyword (TOP,MAX are mot allowed)

Question Posted / pradeep

8 Answers
13225 Views
I also Faced
E-Mail Answers

Answer Posted / pradeep

DECLARE @test TABLE(Empnm VARCHAR(10), Salary VARCHAR(10))

INSERT INTO @test (Empnm, Salary )
SELECT 'A', '200'
UNION ALL
SELECT 'B', '300'
UNION ALL
SELECT 'A', '200'
UNION ALL
SELECT 'B', '300'
UNION ALL
SELECT 'C', '400'
UNION ALL
SELECT 'C', '400'
UNION ALL
SELECT 'E', '100'
UNION ALL
SELECT 'D', '500'

SELECT * FROM @test

SELECT Empnm, Salary, (SELECT COUNT(DISTINCT(SALARY)) FROM
@test AS B WHERE A.Salary <= B.SALARY)
FROM @test AS A
GROUP BY Empnm, Salary
HAVING 4 = (SELECT COUNT(DISTINCT(SALARY)) FROM @test AS B
WHERE A.Salary <= B.SALARY)

Is This Answer Correct ?

9 Yes

2 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

How do I create a trace in sql server?

1037

What is field with example?

916

What is the difference between ‘having’ clause and a ‘where’ clause?

1035

Tell me what are the advantages of using stored procedures?

1001

Can sql servers linked to other servers?

1031

What is the sql case statement used for?

1090

Is natural join and equi join same?

965

How to find related tables in sql server?

932

What are some of the pros and cons of not dropping the sql server builtinadministrators group? : sql server security

974

What is 1nf 2nf?

1005

Do you know what is openxml in sql server?

1063

How can we determine what objects a user-defined function depends upon?

1000

Explain about builtinadministrator?

1049

What is dknf in normalization form?

1049

How to apply filtering criteria at group level with the having clause in ms sql server?

996