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 - --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
