Answer / suraj kedia

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

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.

Answer / biswa

select distinct(e1.sal) from emp e1 where &n=(select count(distinct(e2.sal)) from emp e2 where e1.sal <=e2.sal)

Answer / chandrasekar

Hi Suraj,

Your SQL query is not working? so please try in oracle and
once again load to site.

Thanks for ur help.

Answer / venky

select * from emp e where &n=(select count(distinct(b.sal))
from emp b where e.sal=b.sal);

Answer / tina

select min(sal)
from tablename
where sal in
(select top n-1 sal from tablename order by sal desc)

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

Answer / sree

select sal from emp e where nth=(select count(*) from emp
where sal>e.sal);

What is dynamic cache?

0 Answers  

Data is passed from one active trans and one passive trans into a passive transformation.Is the mapping valid or invalid?

18 Answers   L&T,

In session log thier should not be a single error how will u do

3 Answers   HCL,

what is mean by grouping of condition column in lookup transformation?

1 Answers  

Explain the tuning lookup transformation - informatica

0 Answers   Informatica,

how we can add header and footer to flat files?

2 Answers   Tech Mahindra, Zensar,

I have 2records in target table and this record is deleted in source. Now if I run mapping twice how many records do target will have,  source having 10records. Same with incrementally.  Please  answer me ASAP. 

1 Answers   Cipla, Quintiles,

in source one table is there. id value --- ------ 1 a 2 b 3 c 1 d 2 e 1 f in target i want id value ---- ------ 1 a,d,f 2 b,e 3 f How to implement it in informatica without using normalizer transformation?

2 Answers   IBM,

how to join the two flatfiles using the joiner t/r if there is no matching port?

5 Answers   Cegedim,

How to create the source and target database connections in server manager?

0 Answers   Informatica,

What are the types of caches in lookup? Explain them.

0 Answers  

How to load time dimension?

1 Answers   Ford,