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 |
how to increment dates by 1 in mysql? : Sql dba
what is outer join? what is selef join? what is difference between them? what is cartecion join?
1 Answers Fiserv, Herbinger, Synechron,
Is mariadb a nosql database?
what is the difference between a web-garden and a web-farm? : Sql dba
1. is it possible to use the cursor atttibutes (%found ,% rowcount , %isopen , %notfound ) to our user defined cursor names ....... cursor cursor_name is select * from scott.emp if you use... cursor_name%found , %rowcount ,%isopen,%notfound...will it work... -------------------------- 2.what is the difference between the varray and index by table .. -------- 3. type type_name is table of number(8,3) index by binary_integer; identifier_name type_name; first , last , prior , next ,trim are the methods we can use it for the above type...simillary is there any way to apply for cursors... with thanks and regards..sarao...
What is mutating trigger?How to avoid it??
What is the difference between delete, truncate and drop command?
Why query optimization is needed?
types of exceptions and what is meant by pragma autonomous_transaction ?what is the use.
What does select * from mean in sql?
how to create a new view in mysql? : Sql dba
Write a query to find the name of employees those who have joined on Monday.(based on column hire_date)