To Saurabh
Rajeshwaran, Jeyabal, May 28, 2016 - 5:11 am UTC
Could you help us to understand why don't you want Analytic here ?
May 28, 2016 - 11:07 am UTC
My guess is that analytics have not been covered yet in the course they are on :-)
Understanding
Ghassan, May 28, 2016 - 7:11 pm UTC
Seems that the need is missed?!
Isn't the list of employee having the higher salary per dept?
May 29, 2016 - 3:55 am UTC
Apologies, I misread the question as "show all employees and show the highest salary per dept".
Something like this should do
SQL> select *
2 from scott.emp e
3 where not exists
4 ( select null
5 from scott.emp
6 where deptno = e.deptno
7 and sal > e.sal );
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
4 rows selected.
One record for each department
Saurabh, May 30, 2016 - 6:44 am UTC
Hi Connor,
Thanks for your reply.
The query posted by you return multiple records for each department. But, I want to display only one record from each department(DEPTNO), which will be the details of the employee having maximum salary from that department.
I have achieved the result by rank analytic function. But, I want to get the output without the use of analytic function. Is there a way to achieve that.
May 31, 2016 - 1:27 am UTC
"I want to display only one record from each department(DEPTNO), which will be the details of the employee having maximum salary from that department"
Then you need come up with a better definition of "maximum salary".
*Both* FORD and SCOTT have the highest salary in dept 20, which is why they are shown. Hence you need something that serves as a "tie breaker"
To Jeyabal
Saurabh, May 30, 2016 - 6:50 am UTC
Hi Jeyabal,
I asked cause analytic function are easier to write and are not confusing. And I need to understand other ways to clear up my idea of SQL more.
I want to get more clear understanding of correlated, subqueries, aggregate functions.
Followup
Ghassan, May 31, 2016 - 5:47 am UTC
Tie breaker. . Maybe a pivot could make the definitive solution. Last query and then apply another one on using a pivot.
hi
dhsrmsrsj, December 10, 2019 - 12:00 am UTC
how to get department wise 2nd highest salary from scott.emp
table
December 10, 2019 - 10:53 am UTC
You need to define carefully what you mean by "2nd highest".
Row_number, rank, and dense_rank can all help you. But they all return different results when 2 or more people have the same salary:
select *
from (
select e.*,
row_number() over ( partition by deptno order by sal desc ) rn,
dense_rank() over ( partition by deptno order by sal desc ) dr,
rank() over ( partition by deptno order by sal desc ) rk
from scott.emp e
)
where rn = 2
or dr = 2
or rk = 2
order by deptno, sal desc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN DR RK
7782 CLARK MANAGER 7839 09-JUN-81 2450 - 10 2 2 2
7902 FORD ANALYST 7566 03-DEC-81 3000 - 20 2 1 1
7566 JONES MANAGER 7839 02-APR-81 2975 - 20 3 2 3
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 2 2 2
one more to that list
Rajeshwaran Jeyabal, December 10, 2019 - 11:54 am UTC
...
Row_number, rank, and dense_rank can all help you.
...
One more to that list is NTH_VALUE analytics.
A reader, December 18, 2019 - 5:47 pm UTC
select max(sal),deptno from employees group of deptno
January 06, 2020 - 2:51 am UTC
I don't we're seeing *all* the columns of employee here