I would like to know In what sequence SQL statement are processed...?
(The clauses of the subselect processing)
Thanks
Kevin
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.
follow this link
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))
Why do not you start a new thread for your postings?
It returns the record with 5th highest salary not 4th of 5th 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.
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
>> Please explain me how exactly this works.
i Understood the statement syntactically but logically im a little confused like how this works
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.