How to retrinve the first top salary hoder

[amitpatel66]

1.
SELECT x.salary FROM emp x WHERE 3 > (SELECT COUNT(*) FROM emp WHERE salary > x.salary)


what is salary and x.salary (x is allias )
SELECT COUNT(*) FROM emp WHERE salary > x.salary)
count =6 according to my table
salary >x.salary means
1000>1000
how it is itterate sorry for any stupid queustion

I think you are confused with the concept of correlated sub query. There is not iteration as such. Its like this:

For each value of outer query, the inner query is executed.

For example, The outer query table emp x (x is alias) has values 1000,2000,3000

Now, in the inner query, I am using the emp table, and there are 3 salary in the table ie 1000,2000,3000, so when the values of outer query are compared with the inner query values, then for all the values of outer query, the inner query will be executed.i.e. fthe value 1000 of outer query will be compared with all the values of inner query i.e. 1000,2000,3000, followed by same way for 2000,3000 of the outer query.

For more on correlated subqueries, check here

[karthickkuchanur]

I think you are confused with the concept of correlated sub query. There is not iteration as such. Its like this:

For each value of outer query, the inner query is executed.

For example, The outer query table emp x (x is alias) has values 1000,2000,3000

Now, in the inner query, I am using the emp table, and there are 3 salary in the table ie 1000,2000,3000, so when the values of outer query are compared with the inner query values, then for all the values of outer query, the inner query will be executed.i.e. fthe value 1000 of outer query will be compared with all the values of inner query i.e. 1000,2000,3000, followed by same way for 2000,3000 of the outer query.

For more on correlated subqueries, check here

ok thanku very much sir i will try to learn the basic thing sir,but i am in situation to use the ur query blindly .thank u

[amitpatel66]

ok thanku very much sir i will try to learn the basic thing sir,but i am in situation to use the ur query blindly .thank u

Thats good to learn oracle basics.
You can also have a look at HOWTOS articles in our forum

[Sonu Verma]

with Test_CTE
as
(
SELECT firstname,total amt,dense_rank( ) OVER(ORDER BY totalamt DESC) rn FROM [order]
)

select firstname,total amt from Test_CTE where rn >3 and rn < 10