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 educationalhist orid from preducational order by educationalhist orid,limit 1,3

[amitpatel66]

select educationalhist orid from preducational order by educationalhist orid,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:

[code=oracle]

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

[/code]

[karthickkuchanur]

Try this:

[code=oracle]

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

[/code]

i cant able to uderstand the first query

This my query
SELECT x.EDUCATIONALHI STORYID FROM PREDUCATIONALHI STORY x WHERE 3 > (SELECT COUNT(*) FROM PREDUCATIONALHI STORY WHERE EDUCATIONALHIST ORYID > x.EDUCATIONALHI STORYID)
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