how to findout the 100th maximum salary
Answers were Sorted based on User's Feedback
Answer / suresh
select sal from emp e
where 100=(select count( distinct sal) from emp where
e.sal<=sal)
Is This Answer Correct ? | 10 Yes | 0 No |
Answer / g.sivanagaraju
SELECT DISTINCT(A.SAL)
FROM EMP A
WHERE &N=(SELECT COUNT(DISTINCT(B.SAL))
FROM EMP B
WHERE A.SAL<=B.SAL);
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / sanjay kumar
Select * from
(select distinct salary, dense_rank()over (Order by salary desc) rk from emp)
Where rk=100;
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / cuong nguyen
select min(salary) from (select top 100 distinct salary from
emp order by salary desc)
Is This Answer Correct ? | 3 Yes | 2 No |
Answer / srinu
Hi Jyothi,
The below q uery is suitable for Nth max salary....
SELECT a.sal
FROM emp a
WHERE &N=(SELECT COUNT(DISTINCT b.sal)
FROM emp b
WHERE a.sal<=b.sal);
In the above query u can substitute any value like 1,2,..100 etc instead of &n.
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / aswini
select min(sal) from (select sal from emp order by sal desc) where rownum<=100
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / john bershan
select sgp from (select sgp,dense_rank() over (order by
sgp desc) as rownumber from t_policy_general)
where rownumber = '100';
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / murali
select level,max(sal) from emp where level=&levelno connect by prior sal>sal group by level;
Is This Answer Correct ? | 6 Yes | 6 No |
Answer / gani
SELECT sal FROM table_name
ORDER BY sal DESC
LIMIT(99,1);
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / maroju naveen
select level,max(sal) from emp where level=&levelno
connect by prior sal>sal
group by level;
This is Nth max(sal) query.....
Is This Answer Correct ? | 2 Yes | 2 No |
Can I create table without primary key?
Is left join and outer join same?
what are rollup and cube in t-sql? : Transact sql
what is a database? : Sql dba
how to use case expression? : Sql dba
What is the need of merge statement?
Can we use joins in subquery?
how to insert values like 10:10:00,30:25:00 etc.into table after insert how can i sum the above insert values to get the result as 40:35:00
What is the default isolation level in sql server? : Transact sql
What operating systems are supported by oracle sql developer?
in table there r so many rows write a query which two rows r updated last two day befor?
what is difference between delete and truncate commands? : Sql dba