Topic: SQL*Plus and PL/SQL >> oracle query
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| san |
Posted: Jul 21, 2008 07:09:59 AM |
Total Post: 1
Joined: Jul, 2008
|
Hi
here is a query which gives 5 max salary but I am unable to figure out how it is actually working by using e.sal<=e1.sal and giving me output.
could some one help me out in this regard.
query:-
select distinct sal from emp e where 5=(select count(distinct sal) from emp e1 where e.sal<=e1.sal);
output:-
SAL
----------
2450
SQL> select distinct sal from emp order by sal desc;
SAL
----------
5000
3000
2975
2850
2450
1600
1500
1300
1250
1100
950
800
12 rows selected.
|
|
|
 shake |
| Posted: Jul 24, 2008 02:11:28 AM |  |
|
Total Post: 85
Joined: Jun, 2008
|
To put the same query in another way (to understand it better), I rewrite it as follows.
SELECT DISTINCT sal FROM employee e WHERE (SELECT COUNT(DISTINCT e1.sal) FROM employee e1 WHERE e.sal<=e1.sal)=5;
In this query, we use the same tablename 2 times. It seems to be self joining is done in the process of retrieval of data from that table.
The WHERE condition of the subquery e.sal<=e1.sal is the condition of the self-join. The salary column of the employee table is compared with itself and produces a count value if the condition is satisfied. Here the highest salary gets the count 1 and lowest salary gets maximum count, due to the less than (<=) condition. This arranges the salary column in descending order. If the condition has >= operator, it sorts the column in ascending order. Now, from the values returned by COUNT function and its SELECT subquery, it is possible to retrieve the nth record as the nth salary.
NOTE: If you don't use the DISTINCT keyword inside COUNT function, it is obvious that you will surely get wrong result.
I was clueless how this query works. But after getting the following query from my friend 'PeeJay' (regular viewer of dbapool), I analysed it a step further and could understand the query. This is the query.
SELECT COUNT(DISTINCT e1.sal),e.sal FROM employee e1 JOIN employee e
ON e.sal<=e1.sal GROUP BY e.sal
If it is still not clear, I am happy to give more explanation on this.
Thanks PeeJay.
Thanks san for raising useful doubts.
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Vinoth Kumar | 367 |
| Gopu Gopi | 340 |
| Gitesh Trivedi | 322 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
