How to retrinve the first top salary hoder

[karthickkuchanur]

Hai sir,
I want to retrive the top 3 salary hoder in my table
coloumn name=salary

[amitpatel66]

Hai sir,
I want to retrive the top 3 salary hoder in my table
coloumn name=salary

Please post what you have tried so far?

[karthickkuchanur]

Please post what you have tried so far?

select educationalhistorid from preducational order by educationalhistorid,limit 1,3

[amitpatel66]

select educationalhistorid from preducational order by educationalhistorid,limit 1,3

and what is the error that you are getting?

[karthickkuchanur]

and what is the error that you are getting?

Error: ORA-00933: SQL command not properly ended

[amitpatel66]

Error: ORA-00933: SQL command not properly ended

Thats right because you cannot use LIMIT clause like that in SQL statement

Try this:

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

[karthickkuchanur]

Thats right because you cannot use LIMIT clause like that in SQL statement

Try this:

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

thank u sir that query is working fine but i dobt how it is work,i have another doubt how can retrive the data from 3 to 10

[amitpatel66]

thank u sir that query is working fine but i dobt how it is work,i have another doubt how can retrive the data from 3 to 10

Try this:

 
SELECT empno,salary FROM
(SELECT empno,salary,dense_rank() OVER(ORDER BY SALARY DESC) rnk FROM emp) WHERE rn >= 3 AND rn <=10
 
 

[karthickkuchanur]

Try this:

 
SELECT empno,salary FROM
(SELECT empno,salary,dense_rank() OVER(ORDER BY SALARY DESC) rnk FROM emp) WHERE rn >= 3 AND rn <=10
 
 

i cant able to uderstand the first query

This my query
SELECT x.EDUCATIONALHISTORYID FROM PREDUCATIONALHISTORY x WHERE 3 > (SELECT COUNT(*) FROM PREDUCATIONALHISTORY WHERE EDUCATIONALHISTORYID > x.EDUCATIONALHISTORYID)
i have 6 column

accourding to that how the condition sucess
3>(6) i can t sir

[amitpatel66]

i cant able to uderstand the first query

My first query is a correlated sub-query. You can see I am passing the value of the outer query table to the innser sub-query. So for each value of outer query, the sub-query will be executed.

Eg:

Salary:

1000
2000
3000
4000
5000

Now the execution happens this way:

First, 1000 is passed to the inner sub query and is compared with all the values of salary column, something like:

1000> 1000?, 2000> 1000?,.....till 5000 > 1000? -- From all this check, the count that you get for 1000 is 4, and check for the main WHERE condition, is 3 > 4?? -- No, so 1000 will not get listed in the output.

Same way for all other values, and the last three values that get listed in the output are: 3000, 4000, 5000 which gives you top three salaries

I hope my explanation is clear!!

Post back in case of any issues!!

[subashsavji]

Hai sir,
I want to retrive the top 3 salary hoder in my table
coloumn name=salary

 
SELECT min(sal)
FROM (SELECT sal
FROM emp 
order by sal desc)
WHERE rownum<(&nth_heighest+1); 
//
 
select ename, sal
from (select ename,sal,dense_rank() over(order by sal desc) dr from emp)
where dr = &rnk
//
 

[subashsavji]

Hai sir,
I want to retrive the top 3 salary hoder in my table
coloumn name=salary

 
SELECT min(sal)
FROM (SELECT sal
FROM emp 
order by sal desc)
WHERE rownum<(&nth_heighest+1); 
//
 
select ename, sal
from (select ename,sal,dense_rank() over(order by sal desc) dr from emp)
where dr = &rnk
//
 

[karthickkuchanur]

My first query is a correlated sub-query. You can see I am passing the value of the outer query table to the innser sub-query. So for each value of outer query, the sub-query will be executed.

Eg:

Salary:

1000
2000
3000
4000
5000

Now the execution happens this way:

First, 1000 is passed to the inner sub query and is compared with all the values of salary column, something like:

1000> 1000?, 2000> 1000?,.....till 5000 > 1000? -- From all this check, the count that you get for 1000 is 4, and check for the main WHERE condition, is 3 > 4?? -- No, so 1000 will not get listed in the output.

Same way for all other values, and the last three values that get listed in the output are: 3000, 4000, 5000 which gives you top three salaries

I hope my explanation is clear!!

Post back in case of any issues!!

ok thank u very much sir

[amitpatel66]

ok thank u very much sir

you are welcome :) .

[karthickkuchanur]

you are welcome :) .

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

[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,totalamt,dense_rank() OVER(ORDER BY totalamt DESC) rn FROM [order]
)

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