There may be ties, so a little change:
select * from (
select e.emp_id, e.e_name, s.sal
,rank() over (order by s.sal) sal_rank
from emp_master e
,sal_master s
where s.emp_id = e.emp_id
order by sal) a
where a.sal_rank = 3
On Aug 5, 12:55 am, Trail <[email protected]> wrote:
> Hi Harry,
>
> We could really use a little more info - like what happens in the case
> of a tie. I have assumed that you would like all records with the
> third highest salary. Also assuming a 1-1 relationship between sal
> and emp (employee has one salary).
>
> create table emp_master (emp_id number, e_name varchar2(50));
>
> create table sal_master (emp_id number, sal number);
>
> insert into emp_master values (1, 'Fred');
>
> insert into emp_master values (2, 'Barney');
>
> insert into emp_master values (3, 'Wilma');
>
> insert into emp_master values (4, 'Betty');
>
> insert into emp_master values (5, 'Pebbles');
>
> insert into sal_master values (5, 1000);
>
> insert into sal_master values (4, 7100);
>
> insert into sal_master values (3, 2500);
>
> insert into sal_master values (2, 10000);
>
> insert into sal_master values (1, 5000);
>
> select * from (
> select e.emp_id, e.e_name, s.sal
> ,dense_rank() over (order by s.sal) sal_rank
> from emp_master e
> ,sal_master s
> where s.emp_id = e.emp_id
> order by sal) a
> where a.sal_rank = 3
>
> On Aug 4, 1:51 am, Harry79 <[email protected]> wrote:
>
> > Hello i do have one problem with SQL Query.
> > There are total two table and i would like to get 3rd highest salary.
>
> > Table 1: emp_master
> > Column : ename,emp_id
>
> > Table 2: Sal_master
> > Column : sal,emp_id
>
> > So how can i get the third highest salary?
> > emp_id is PK
>
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
