Sequence of SQL statement processed

I would like to know In what sequence SQL statement are processed...?
(The clauses of the subselect processing)

Thanks
Kevin

From inner block to outer block.

[QUOTE=kevinphilip;29063]I would like to know In what sequence SQL statement are processed...?
(The clauses of the subselect processing)

Thanks
Kevin[/QUOTE]
follow this [URL="http://www.adp-gmbh.ch/ora/concepts/sql_stmt.html"]link[/URL]

[QUOTE=debasisdas;29069]From inner block to outer block.[/QUOTE]

Not always I think..

Generally for subqueries the optimizer first runs the inner most block first then depending upong the result set , process the outer block.

But in case of EXISTS, optimizer runs main query first and then applies dataset to the subquery.

[QUOTE=krishnaindia2007;29290]Not always I think..

Generally for subqueries the optimizer first runs the inner most block first then depending upong the result set , process the outer block.

But in case of EXISTS, optimizer runs main query first and then applies dataset to the subquery.[/QUOTE]

Yes you are right . That depends on the query.

Could you please explain how the count function works here? question)sql query to get 4th or 5th maximum value from a table ans: select * from jobhistory x where (5=(select count(distinct(y.salary)) from jobhistory y where y.salary>=x.salary))

[QUOTE=kirandstyle;29448]Could you please explain how the count function works here? question)sql query to get 4th or 5th maximum value from a table ans: select * from jobhistory x where (5=(select count(distinct(y.salary)) from jobhistory y where y.salary>=x.salary))[/QUOTE]

Why do not you start a new thread for your postings?

It returns the record with [B]5th[/B] highest salary not [B]4th of 5th [/B]maximum value.

Count returns number of records that meets specified codition.

It is an example for correlated query i.e inner query is evaluated once for each row of outer query.

Each time it checks whether the salary is 5th highest salary or not and if it is fifth highest sal then displays particulars of that record.

[QUOTE=krishnaindia2007;29458]Why do not you start a new thread for your postings?

It returns the record with [B]5th[/B] highest salary not [B]4th of 5th [/B]maximum value.

Count returns number of records that meets specified codition.

It is an example for correlated query i.e inner query is evaluated once for each row of outer query.

Each time it checks whether the salary is 5th highest salary or not and if it is fifth highest sal then displays particulars of that record.[/QUOTE]
could you please explain me how does the below work. suppose in a situation we have a table called jobhistory where, we have values in salary column as (3000,4000,5000,6000,7000)
so in order to calculate the nth highest salary i used the abve sql query...but i m confused like how does the part of the sql query i.e., y.salary>=x.salary works.
does this statement means tht each value of salary column in jobhistory table y looks for >= condition in each value of salary coulumn in table x.
if so...suppose say 3000 of y table is comparing itself to all values of salary in table x
then 3000>=3000
3000>=4000
3000>=5000
3000>=6000
3000>=7000

4000>=3000
4000>=4000
4000>=5000
4000>=6000
4000>=7000
and sooooo on....
if this is correct
then in statement to calculate the nth or 5th highest salary
i.e.,select * from jobhistory x where (5=(select count(distinct(y.salary)) from jobhistory y where y.salary>=x.salary))

how the statement count will work
how does the 1st highest salary is determined from the above combinations.
Sir,
Please explain me how exactly this works.
i Understood the statement syntactically but logically im a little confused like how this works

[B]>> Please explain me how exactly this works.
i Understood the statement syntactically but logically im a little confused like how this works[/B]


The correlated subquery performs the following steps for each row of the parent query:
1. The sal of the row is determined (i.e. x.salary from parent statement).
2. Then it compares that x.salary with each distinct salary value of jobhistory table.
3. For example if x.salary is 5000. Then it checks no of records in jobhistory table having more than 5000. If the count is exactly 5 then the codition becomes true.
4. Then outer query or parent statement is executed.

I hope now it is clear.

[QUOTE=kirandstyle;29448]Could you please explain how the count function works here? question)sql query to get 4th or 5th maximum value from a table ans: select * from jobhistory x where (5=(select count(distinct(y.salary)) from jobhistory y where y.salary>=x.salary))[/QUOTE]

Please not hijack threads started by others .
Start a thread of your own for a new question in the related forum

Regards
Moderator.