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 |
which types of join is used in sql widely? : Sql dba
What is the largest value that can be stored in a byte data field?
What is difference between cursor and trigger?
Does pl/sql support create command?
Is oracel sql developer written in java?
How to avoid using cursors?
How does a self join work?
What are the new features in Oracle 10g. Compared to Oracle 9i?
What is database sql?
I want to create synonym for table emp but in my pc it is giving insufficient previliges.I am using user scott.Please suggest me.
What is the maximum number of triggers,can apply to a single table?
What is break?