consider a table which contain 4 columns,ename,eno,sal and
deptno, from this table i want to know ename who having
maximum salary in deptno 10 and 20.
Answers were Sorted based on User's Feedback
Answer / purushotham
select ename from table where sal in
(select max(sal) from table_name
where deptno in ('10','20')
group by deptno);
Is This Answer Correct ? | 16 Yes | 8 No |
Answer / srilekha
select ename from table where sal=(select max(sal) from
table) and depetno in (10,20)
Is This Answer Correct ? | 27 Yes | 20 No |
Answer / rohan
select empno from emp where (sal,deptno) in ( select max
(sal),deptno from emp where deptno in (10,20) group by
deptno)
Is This Answer Correct ? | 13 Yes | 6 No |
Answer / rashmi_raju
SELECT ename FROM emp WHERE sal =(SELECT MAX(sal) FROM emp
WHERE deptno=10)
UNION
(SELECT ename FROM emp WHERE sal =(SELECT MAX(sal) FROM emp
WHERE deptno=20))
Is This Answer Correct ? | 6 Yes | 0 No |
Answer / guest
Select emp_name from emp where sal =(Select Max(sal) from
emp where deptno=10) and deptno=10 union
Select emp_name from emp where sal =(Select Max(sal) from
emp where deptno=20)and deptno=20
Is This Answer Correct ? | 6 Yes | 1 No |
Answer / kishore
select * from em where sal in (select max(sal) from em
where dno in (10,20) group by dno) and dno in (10,20)
where em = table name
dno = department number
sal = salary
Is This Answer Correct ? | 4 Yes | 3 No |
Answer / anil pednekar
select table1.ename from (select deptno, max(sal) as sal1
from table1 group by deptno having deptno in(1,2)) as T1,
table1 where T1.deptno=table1.deptno and
T1.sal1=table1.sal
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / ashim
select * from (select id,dept,sal,dense_rank() over
(partition by dept order by sal desc) p from a_tab1)
where p=1
....by using this program we can find out the nth salary of
different department jus give p=n
Is This Answer Correct ? | 1 Yes | 0 No |
select last_name,salary from employees where (salary,department_id) in ( select max(salary),
department_id from employees where department_id in (10,20) group by
department_id)
/
Output
LAST_NAME SALARY
------------------------- ----------
Hartstein 13000
Whalen 4400
Is This Answer Correct ? | 1 Yes | 0 No |
SELECT A.EMP_NAME, SUM(B.MAX_SALARY) AS MAX_SAL, B.EMP_DEPT_NO FROM EMP_DETAILS A,
(SELECT MAX(EMP_SAL) AS MAX_SALARY, EMP_DEPT_NO FROM EMP_DETAILS WHERE EMP_DEPT_NO IN (10,20)
GROUP BY EMP_DEPT_NO) B
WHERE
A.EMP_SAL = B.MAX_SALARY
AND A.EMP_DEPT_NO = B.EMP_DEPT_NO
GROUP BY A.EMP_NAME, B.EMP_DEPT_NO
Is This Answer Correct ? | 0 Yes | 0 No |
When sql appeared?
what are the advantages and disadvantages of views in a database? : Sql dba
Why is sql*loader direct path so fast?
What is the meaning of disabling a trigger?
select 1,col1,col2 from table1. output?
What are the rules to be applied to nulls whilst doing comparisons?
Mention what does plv msg allows you to do?
How can I speed up sql query?
Which sql statement is used to delete data from a database?
The in operator may be used if you know the exact value you want to return for at least one of the columns.
What is the difference between cluster and non-cluster index?
What is meant by cursor in sql?