On Aug 5, 8:57 am, Trail <[email protected]> wrote:
> I was thinking that using dense_rank would be more appropriate for
> resolving ties, as the use of rank, if you have 2 or more values tied
> at second (or 3 or more at first) would result in *no* record having
> the third highest (the rank would "skip" a value).
>
> Just my $.02
>
> On Aug 5, 10:42 am, kevin <[email protected]> wrote:
>
>
>
> > 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- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
All of this effort to do someone else's homework ...
David Fitzzjarrell
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
