Please get the 4 th maximum salary from a table without
using any sql keyword (TOP,MAX are mot allowed)
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Answer / shashank tayal
select salary from table_name order by desc limit 3,1;
Is This Answer Correct ? | 0 Yes | 2 No |
What is the use of toad or sqldbx.?
Give main differences between "Truncate" and "Delete".
Thanks to some maintenance being done, the sql server on a failover cluster needs to be brought down. How do you bring the sql server down?
How to use "begin ... End" statement structures in ms sql server?
What is the purpose of UPDATE STATISTICS?
to explain sql server 2000 architecture & authentication
Can we shrink data file in sql server?
what is a schema in sql server 2005? Explain how to create a new schema in a database? : Sql server database administration
can you instantiate a com object by using t-sql? : Sql server database administration
How to find out name of all employees who has salary less than 200 Rs.?
What is model database? : SQL Server Architecture
what is a stored procedure and trigger?