Query to get max and second max in oracle in one query ?
- 34 Answers
- 57584 Views
- CTS, Keane India Ltd, TCS, Wipro, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / shashank yadav
Select Max(sal) from emp
where sal < any (select sal from emp);
| Is This Answer Correct ? | 3 Yes | 1 No |
Answer / assdads
five times tapping shift key helps to go woth out a mouse
by using number key.you can copy data in form by pressing
shift+insert.
| Is This Answer Correct ? | 2 Yes | 1 No |
Answer / raghav
SELECT MAX(SAL)
FROM EMPLOY
WHERE SAL < ( SELECT MAX(SAL) FROM EMPLOY )
| Is This Answer Correct ? | 5 Yes | 4 No |
Answer / vaibhav
SELECT Sal
FROM (Select Sal from Emp_Salary order by Sal desc)
WHERE rownum < =2;
Above query will fetch two rows. First row is the max salary
and second row second max.
IF we only need to find second max, then
SELECT Min(Sal)
FROM (Select Sal from Emp_Salary order by Sal desc)
WHERE rownum < =2;
| Is This Answer Correct ? | 4 Yes | 3 No |
Answer / amit golia
SELECT min(sal) FROM(SELECT DISTINCT(sal) FROM emp ORDER
BY sal DESC) WHERE ROWNUM<=2;
| Is This Answer Correct ? | 2 Yes | 1 No |
Answer / kevin sharpless
-- This query wiil return any number of the n items by
using the max/decode statements
select max(decode(rnum,1,salary,null)) max1
,max(decode(rnum,2,salary,null)) max2
from (
select 1 grp_col
,salary
,row_number() over (order by salary asc) rnum
from (
select distinct salary
from emp
)
)
group by grp_col
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / haddy
select ename,salary from (select distinct(salary) from
employee order by salary desc) where rownum<=2
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / roopa
SELECT MAX(E1.sal), MAX(E2.sal)
FROM emp E1, (SELECT sal FROM emp
WHERE sal NOT IN ( SELECT MAX(sal) FROM emp)) E2
| Is This Answer Correct ? | 7 Yes | 7 No |
Answer / zyxw
for removing lines between word document and to get in a
paragarph manner
first do ctrl+h and then put ^p and leave replace with
empty.and go for
replace or replace all depending on the situation.
| Is This Answer Correct ? | 2 Yes | 2 No |
Answer / deepa
select salary from (select salary from table_name order by salary desc) where rownum<3;
| Is This Answer Correct ? | 1 Yes | 1 No |
What is flashback in Oracle?
What are a query and state the different types of queries and their uses?
Please explain oracle data types with examples?
What happens to the current transaction if a ddl statement is executed?
Difference between open_form and call_form in oracle.
Explain the use of file option in exp command.
types of indexes and the rationale behind choosing a particular index for a situation.
How to load data through external tables?
What is an Oracle Instance?
How can you use check constraints for self referential integrity?
What are cursor?where are they used?
What is a sub query and what are the different types of subqueries?
Oracle General 
