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
13455 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

What is bcp? When does it use?

986

What stored by the msdb?

1044

Explain what is raid and what are different types of raid levels?

1087

What are sql servers used for?

1110

What is a full text index?

964

What is log in sql server?

1201

How can you set the threshold at which sql server will generate keysets asynchronously?

1049

What is a covering index?

1039

What does set rowcount do?

958

What are the recovery models for a database?

1144

How to create an multi-statement table-valued function?

1046

Explain the ways to controlling cursor behavior?

966

What is a trigger what are the advantages of trigger?

1062

What is the difference between a unique key and primary key?

1002

Does hive support indexing?

999