Home » SQL & PL/SQL » SQL & PL/SQL » Please help in understanding this SQL statement for getting the Top 3 salaried employees
| Please help in understanding this SQL statement for getting the Top 3 salaried employees [message #167234] |
Wed, 12 April 2006 00:34  |
sudhirbhatti
Messages: 5
Registered: June 2005
Location: Noida,India
|
Junior Member |
|
|
I have a table which gives employees and their salaries.
I want the details of top 3 salaried employees.
There are two ways of doing it.
One through analytical func. and the other is given below.
Can someone explain how does this query works internally?
Which portion is evaluated first?
how the comparison is being done between the e1 & e2 values?
select e1.name, e1.sal from emp1 e1,emp1 e2
where e1.sal<=e2.sal
group by e1.sal,e1.name
having count(e2.sal)<=3;
|
|
|
|
| Re: Please help in understanding this SQL statement for getting the Top 3 salaried employees [message #167292 is a reply to message #167234] |
Wed, 12 April 2006 05:07  |
 |
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
|
Senior Member |
|
|
Just have a look ...
15:25:59 SQL> SELECT e.ename, e.sal from emp e;
ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
SCOTT 3000
KING 5000
FORD 3000
15:23:04 SQL> SELECT e.ename, e.sal,e1.sal FROM emp e,emp e1
15:23:14 2 WHERE e.sal<=e1.sal
15:23:14 3 order by e.ename, e.sal;
ENAME SAL SAL
---------- ---------- ----------
BLAKE 2850 2975
BLAKE 2850 2850
BLAKE 2850 3000
BLAKE 2850 5000
BLAKE 2850 3000
FORD 3000 3000
FORD 3000 3000
FORD 3000 5000
JONES 2975 2975
JONES 2975 3000
JONES 2975 3000
JONES 2975 5000
KING 5000 5000
SCOTT 3000 3000
SCOTT 3000 5000
SCOTT 3000 3000
16 rows selected.
Elapsed: 00:00:01.00
15:23:15 SQL> SELECT e.ename, e.sal,e1.sal FROM emp e,emp e1
15:23:23 2 WHERE e.sal<=e1.sal
15:23:23 3 GROUP BY e.ename, e.sal,e1.sal
15:23:23 4 HAVING count(e1.sal)<=3;
ENAME SAL SAL
---------- ---------- ----------
FORD 3000 3000
FORD 3000 5000
KING 5000 5000
BLAKE 2850 3000
BLAKE 2850 5000
BLAKE 2850 2850
BLAKE 2850 2975
JONES 2975 3000
JONES 2975 5000
JONES 2975 2975
SCOTT 3000 3000
SCOTT 3000 5000
12 rows selected.
Elapsed: 00:00:00.06
15:23:24 SQL> ed
Wrote file afiedt.buf
1 SELECT e.ename, e.sal FROM emp e,emp e1
2 WHERE e.sal<=e1.sal
3* GROUP BY e.ename, e.sal
15:23:42 SQL> /
ENAME SAL
---------- ----------
FORD 3000
KING 5000
BLAKE 2850
JONES 2975
SCOTT 3000
Elapsed: 00:00:00.02
15:23:44 SQL> ed
Wrote file afiedt.buf
1 SELECT e.ename, e.sal,count(*) FROM emp e,emp e1
2 WHERE e.sal<=e1.sal
3* GROUP BY e.ename, e.sal
15:24:23 SQL> /
ENAME SAL COUNT(*)
---------- ---------- ----------
FORD 3000 3
KING 5000 1
BLAKE 2850 5
JONES 2975 4
SCOTT 3000 3
Elapsed: 00:00:00.02
15:24:25 SQL> ed
Wrote file afiedt.buf
1 SELECT e.ename, e.sal,count(*) FROM emp e,emp e1
2 WHERE e.sal<=e1.sal
3 GROUP BY e.ename, e.sal
4* having count(*)<=3
15:25:10 SQL> /
ENAME SAL COUNT(*)
---------- ---------- ----------
FORD 3000 3
KING 5000 1
SCOTT 3000 3
Elapsed: 00:00:00.02
15:25:12 SQL> ed
Wrote file afiedt.buf
1 SELECT e.ename, e.sal,count(distinct e1.sal) FROM emp e,emp e1
2 WHERE e.sal<=e1.sal
3 GROUP BY e.ename, e.sal
4* having count(distinct e1.sal)<=3
15:25:58 SQL> /
ENAME SAL COUNT(DISTINCTE1.SAL)
---------- ---------- ---------------------
FORD 3000 2
KING 5000 1
JONES 2975 3
SCOTT 3000 2
Elapsed: 00:00:00.03
15:25:59 SQL>
Is it clear now ... ?
Rajuvan.
[Updated on: Wed, 12 April 2006 05:14] Report message to a moderator |
|
|
|
|
|
| Re: Please help in understanding this SQL statement for getting the Top 3 salaried employees [message #167351 is a reply to message #167234] |
Wed, 12 April 2006 09:53 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Rajavu1 is on the right track, and has taken the right approach. Basically break it up into pieces and try to visualize those pieces and how they fit together. Here is another demo in case it helps.
I'll also say that this must probably be for a class, because it is an odd way in my mind to come up with the answer. But, that is the great thing about SQL is that there are lots of ways to attack a problem, and it is helpful to be aware of the different approaches.
MYDBA >
MYDBA > -- Uses a strange way to get details of people with the top three salaries.
MYDBA > -- I would prefer to use analytics and/or inline views, and would never have
MYDBA > -- come up with this approach on my own, although it does actually work.
MYDBA > -- To understand how it works, deconstruct the query one step at a time.
MYDBA >
MYDBA > -- original query, modified to use the standard emp table in the scott schema
MYDBA >
MYDBA > select e1.ename, e1.sal
2 from emp e1,emp e2
3 where e1.sal<=e2.sal
4 group by e1.sal,e1.ename
5 having count(e2.sal)<=3;
ENAME SAL
---------- ----------
KING 5000
SCOTT 3000
FORD 3000
MYDBA >
MYDBA > -- when you have a function in the having clause, but not in the select
MYDBA > -- clause, you can add it to the select clause without any other changes
MYDBA >
MYDBA > select e1.ename, e1.sal, count(e2.sal)
2 from emp e1,emp e2
3 where e1.sal<=e2.sal
4 group by e1.sal,e1.ename
5 having count(e2.sal)<=3;
ENAME SAL COUNT(E2.SAL)
---------- ---------- -------------
KING 5000 1
SCOTT 3000 3
FORD 3000 3
MYDBA >
MYDBA > -- the query concept is really backwards, because the e2 salaries are
MYDBA > -- the ones that are greater than the e1 salaries, meaning you want at
MYDBA > -- most 3 (<=3). without the having clause, all of counts are shown.
MYDBA >
MYDBA > select e1.ename, e1.sal, count(e2.sal)
2 from emp e1,emp e2
3 where e1.sal<=e2.sal
4 group by e1.sal,e1.ename;
ENAME SAL COUNT(E2.SAL)
---------- ---------- -------------
TURNER 1500 8
ALLEN 1600 7
KING 5000 1
MARTIN 1250 11
WARD 1250 11
MILLER 1300 9
SMITH 800 14
ADAMS 1100 12
CLARK 2450 6
SCOTT 3000 3
BLAKE 2850 5
JAMES 950 13
JONES 2975 4
FORD 3000 3
14 rows selected.
MYDBA >
MYDBA > -- here it is without the group by, but ordered by the group by columns,
MYDBA > -- and showing what is being counted by the group by count function: e2 sal
MYDBA >
MYDBA > select e1.ename, e1.sal, e2.sal
2 from emp e1,emp e2
3 where e1.sal<=e2.sal
4 order by e1.sal, e1.ename;
ENAME SAL SAL
---------- ---------- ----------
SMITH 800 1250
SMITH 800 1250
SMITH 800 1100
SMITH 800 950
SMITH 800 800
SMITH 800 1300
SMITH 800 5000
SMITH 800 1600
SMITH 800 2450
SMITH 800 2850
SMITH 800 2975
SMITH 800 3000
SMITH 800 3000
SMITH 800 1500
JAMES 950 1300
JAMES 950 1250
JAMES 950 1250
JAMES 950 1100
JAMES 950 950
JAMES 950 1500
JAMES 950 5000
JAMES 950 3000
JAMES 950 3000
JAMES 950 2975
JAMES 950 2850
JAMES 950 2450
JAMES 950 1600
ADAMS 1100 1100
ADAMS 1100 1250
ADAMS 1100 1250
ADAMS 1100 1300
ADAMS 1100 3000
ADAMS 1100 5000
ADAMS 1100 2975
ADAMS 1100 2850
ADAMS 1100 2450
ADAMS 1100 1600
ADAMS 1100 1500
ADAMS 1100 3000
MARTIN 1250 1250
MARTIN 1250 1250
MARTIN 1250 1300
MARTIN 1250 1500
MARTIN 1250 1600
MARTIN 1250 2450
MARTIN 1250 2850
MARTIN 1250 2975
MARTIN 1250 3000
MARTIN 1250 3000
MARTIN 1250 5000
WARD 1250 1250
WARD 1250 1250
WARD 1250 1300
WARD 1250 1500
WARD 1250 1600
WARD 1250 5000
WARD 1250 2850
WARD 1250 2975
WARD 1250 3000
WARD 1250 3000
WARD 1250 2450
MILLER 1300 1500
MILLER 1300 1300
MILLER 1300 1600
MILLER 1300 5000
MILLER 1300 3000
MILLER 1300 3000
MILLER 1300 2975
MILLER 1300 2850
MILLER 1300 2450
TURNER 1500 1500
TURNER 1500 3000
TURNER 1500 3000
TURNER 1500 2975
TURNER 1500 2850
TURNER 1500 2450
TURNER 1500 1600
TURNER 1500 5000
ALLEN 1600 1600
ALLEN 1600 5000
ALLEN 1600 3000
ALLEN 1600 3000
ALLEN 1600 2975
ALLEN 1600 2850
ALLEN 1600 2450
CLARK 2450 3000
CLARK 2450 2975
CLARK 2450 2850
CLARK 2450 2450
CLARK 2450 3000
CLARK 2450 5000
BLAKE 2850 3000
BLAKE 2850 3000
BLAKE 2850 2975
BLAKE 2850 2850
BLAKE 2850 5000
JONES 2975 2975
ENAME SAL SAL
---------- ---------- ----------
JONES 2975 3000
JONES 2975 3000
JONES 2975 5000
FORD 3000 5000
FORD 3000 3000
FORD 3000 3000
SCOTT 3000 5000
SCOTT 3000 3000
SCOTT 3000 3000
KING 5000 5000
107 rows selected.
MYDBA >
MYDBA > -- and here is the raw data so can visually double check the results
MYDBA >
MYDBA > select e1.ename, e1.sal
2 from emp e1
3 order by e1.sal desc;
ENAME SAL
---------- ----------
KING 5000
FORD 3000
SCOTT 3000
JONES 2975
BLAKE 2850
CLARK 2450
ALLEN 1600
TURNER 1500
MILLER 1300
WARD 1250
MARTIN 1250
ADAMS 1100
JAMES 950
SMITH 800
14 rows selected.
MYDBA >
MYDBA > set echo off;
|
|
|
|
Goto Forum:
Current Time: Mon Sep 08 06:37:31 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
