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 dml command?
Table student containing 2 columns,Join date,Employee name. Under join date 4 rows r ter =1-jan-2008,2-feb-2008,3-mar- 2008,4-feb-2008.Under Employee name 4 difeerent names jaison,robin,binoy,rahul Result set is, Table containing 4-column name=jan,feb,mar,april,,beneath these months count is given as 1,2,1,0 means these counts representing number of emplooyees joined in a month(january 1employee,february 2 employee,march 1 employee,april 0 employee) Can you give me the required sql query
How to remove duplicate rows from table except one?
How to run sql server 2005 books online on your local system?
What are indexes? When do you need to create Indexes?
4 Answers CarrizalSoft Technologies, HP,
Why we use functions in sql server?
What is field in algebra?
What does <> symbol mean?
how can ur insert the emp table
What is best institute to Learn DotNET And SQL in chennai?
Explain the use of containers in ssis and also their types?
How many clustered indexes can be created on a table? I create a separate index on each column of a table. what are the advantages and disadvantages of this approach?