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
12968 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 3nf normalization?

928

How to optimize stored procedures in sql server?

1125

What is sql server replication? : sql server replication

925

Can sql servers link to other servers?

1023

What is partitioned view?

855

What is difference between Datepart() and Datename() in SqlServer?

1158

How to create dbo table in sql server?

901

When does a workload on SQL Azure get throttled?

129

What are the types of lock supported by ?

996

Explain about nested stored procedure?

978

How do you manipulate data?

850

What is user-defined function?

916

How you can change a cross join into an inner join?

912

What is indexing explain it with an example?

935

How many non clustered indexes there can be on table ?

863