Interested to Buy Any Domain ? << Click Here >> for more details...
What is the query to find nth highest salary?
What is the use of cursors?
- 8 Answers
- 20955 Views
- Keane India Ltd, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
There are 3 ways to find out the nth highest salary in a
given table (e.g. emp) as below;
1) select distinct sal from emp e1 where &n=(select count
distinct sal from emp e2 where e1.sal <=e2.sal);
2) select empno,enaame,sal,deptno,rank () over (order by sal
desc) as ra from emp where ra=&n;
3) select empno,enaame,sal,deptno,dense_rank () over (order
by sal desc) as ra from emp where ra=&n;
All the above methods are tried n tested....it works
successfully.
What is a Cursor.
When a query is executed in oracle, a result set is produced
and stored in the memory. Oracle allows the programmer to
access this result set in the memory through cursors.
Why use a cursor?
Many times, when a query returns more than one row as a
result, we might want to go through each row and process the
data in different way for them. Cursor is handy here.
Types of cursors:
Oracle PL/SQL declares a cursor implicitly for all queries
and DML statements (including queries that return only one
row). But in most of the cases we don’t use these cursors
for queries that return one row. Based on this, there are 2
types of cursors
1. Explicit cursor
2. Implicit cursor
| Is This Answer Correct ? | 13 Yes | 2 No |
Answer / ram
1. nth highest salary
-> select *from emp e where &n =(select count(distinct sal)
from emp m where e.sal <= m.sal);
2. -> Cursor.
When a query is executed in oracle, a result set is
produced and stored in the memory. Oracle allows the
programmer to access this result set in the memory through
cursors.
use a cursor?
Many times, when a query returns more than one row as a
result, we might want to go through each row and process
the data in different way for them. Cursor is handy here.
| Is This Answer Correct ? | 4 Yes | 0 No |
Answer / biswa
select distinct(e1.sal) from emp e1 where &n=(select count(distinct(e2.sal)) from emp e2 where e1.sal <=e2.sal)
| Is This Answer Correct ? | 2 Yes | 0 No |
Hi Suraj,
Your SQL query is not working? so please try in oracle and
once again load to site.
Thanks for ur help.
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / venky
select * from emp e where &n=(select count(distinct(b.sal))
from emp b where e.sal=b.sal);
| Is This Answer Correct ? | 1 Yes | 3 No |
Answer / tina
select min(sal)
from tablename
where sal in
(select top n-1 sal from tablename order by sal desc)
| Is This Answer Correct ? | 0 Yes | 2 No |
Answer / sbvp
select * from table name where sal in ( select * from
(select distinct sal from tablename order by sal desc)
where rownum<=&n);
if there is any another query plz tell me r mail me
| Is This Answer Correct ? | 2 Yes | 6 No |
Answer / sree
select sal from emp e where nth=(select count(*) from emp
where sal>e.sal);
| Is This Answer Correct ? | 5 Yes | 12 No |
What is the difference between OLTP and ODS?
scenario
What are the main features of Oracle 8i with context to datawarehouse?
What is DTM process?
How to recover sessions in concurrent batches?
what is the size of u r project? and what is the size of source file?
How could we generate the sequence of key values without using sequence generator transformation in the target ??
12 Answers TCS, Tech Mahindra,
Hi, This is Ranadheer. How to load fact tables using dimeension tables. Please anyone give me a answer with example. Thanks in Advance.
when informatica 8 series released ?
can you please explain me pre session and post session options?
Please let me know how to make encryption and decryption with example?
how will u take the data from clint server and next what u do in datamodeling what u will u do in staging area what u will u do next what u are doing please give the step by step by process
Teradata 
