Please get the 4 th maximum salary from a table without using any sql keyword

Categories >> Software >> Databases >> SQL Server
Suggest New Category

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

Question Posted / ayanb1302@yahoo.com

8 Answers
10948 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

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

Answer / 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

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

Answer / gajendra

select * from (select *,row_number()over (order by salary
asc)as a from Employee)as b where b.a=4

Is This Answer Correct ?

8 Yes

1 No

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

Answer / v rajkumar

select salary from Emp a where 4 = (select count(distinct
(salary)) from Emp b where b.salary>= a.salary)

Is This Answer Correct ?

4 Yes

1 No

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

Answer / sumathy

Create Table 'Employee' with columns 'Emp_Name'
and 'Salary'. And, Insert some data.....

Cursor:
declare Cursor_Name cursor scroll
for select salary from Emploee order by salary desc
open Cursor_Name
fetch absolute 2 from Cursor_Name
deallocate Cursor_Name

Is This Answer Correct ?

2 Yes

2 No

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

Answer / sumathy

Am Sumathy the above answer was posted by me.

For the above Answer 'absolute 2' will fetch 2nd maximum
value.

To fetch 4th maximum value use 'absolute 4'
To fetch 'n'th maximum value use 'absolute n'

Is This Answer Correct ?

1 Yes

1 No

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

Answer / veeresh kethari

with SalCTE (EMPID,Ename,Sal,Num)as

(select *,row_number() over(order by sal desc) num from emp)

select * from SalCTE where Num=4

Is This Answer Correct ?

1 Yes

1 No

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

Answer / suresh

select payout,emp_cd from empmas e where 2>(select count(payout) from empmas s where s.payout>e.payout);

Is This Answer Correct ?

1 Yes

2 No

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

Answer / shashank tayal

select salary from table_name order by desc limit 3,1;

Is This Answer Correct ?

0 Yes

2 No

Post New Answer