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
13273 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 to drop an existing stored procedure in ms sql server?

1032

What is scalar user-defined function?

1104

How do I shrink an ldf file?

980

What is the difference between primary key and unique constraints?

964

define and explain the differences between clustered and non-clustered indexes.

1196

What is the data type of time?

963

What are drilldown reports?

155

What is the difference between migration and upgradation in sql server?

1097

What are the differences between stored procedure and the dynamic sql?

1096

how many type of indexing in database?

981

What protocol does sql server use?

947

What are the requirements to use odbc connections in php scripts?

1120

How to query multiple tables jointly?

991

What is the importance of a recovery model?

1049

What is a full text index?

925