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 |
How many types of Cursor in SQL SERVER?
How do I completely remove sql server instance?
How to get a list of columns in a view using the "sp_help" stored procedure?
What is policy based management (pbm)? : sql server database administration
How to update existing rows in a table?
What is use of attributehierarchyenabled? : sql server analysis services, ssas
Stored Procedure returns data from multiple tables. How to access it in your ASP.Net code?
Does the unique constraint create an index?
How extra digits are handled with numeric data type literals?
We need to perform what steps in the following order to work with a cursor?
What is the process of normalization?
Explain Normalization and DE normalization
Oracle (3259)
SQL Server (4518)
MS Access (429)
MySQL (1402)
Postgre (483)
Sybase (267)
DB Architecture (141)
DB Administration (291)
DB Development (113)
SQL PLSQL (3330)
MongoDB (502)
IBM Informix (50)
Neo4j (82)
InfluxDB (0)
Apache CouchDB (44)
Firebird (5)
Database Management (1411)
Databases AllOther (288)