Home » SQL & PL/SQL » SQL & PL/SQL » how query is working
how query is working [message #209511] Fri, 15 December 2006 05:02 Go to next message
amit_vass2000
Messages: 52
Registered: December 2006
Location: Delhi
Member
SQL> select * from emp e where 1=(select count(*) from emp where sal>e.sal);

EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO COMM
--------- ---------- --------- --------- --------- --------- --------- ---------
7558 Amit Analyst 456 15-MAY-78 19775 90

Plz tell me how this query is working.
Re: how query is working [message #209517 is a reply to message #209511] Fri, 15 December 2006 05:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It is reading the EMP table, and for each row, it is counting the number of records in EMP where the salary is bigger than the salary of the record it is currently looking at.
If this number is 1, then it will return that row.
Re: how query is working [message #209518 is a reply to message #209511] Fri, 15 December 2006 05:20 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
How means?
It is a correlated query.
The inner query will run for each row of the outer query.
So, it will display the row[s] which will be having only 1 row with higher sal than this row.
Means it will give the rows with second highest salary, if there is only one row with the highest salary.

By
Vamsi
Re: how query is working [message #209521 is a reply to message #209511] Fri, 15 December 2006 05:48 Go to previous message
amit_vass2000
Messages: 52
Registered: December 2006
Location: Delhi
Member
Thanks Sir, it helps me a lot
Previous Topic: execute immediate error
Next Topic: case in oracle
Goto Forum:
  


Current Time: Mon Dec 05 21:23:04 CST 2016

Total time taken to generate the page: 0.09543 seconds