Jblue R
asked on
selecting highest numbered object Oracle 12c
Need to select the slip with the largest number when grouped by stock from objects below:
horse stock slip
100 4 8
101 5 7
all pointers & suggestions appreciated...
create table tab1(horse number, stock number);
insert into tab1 values(100,4);
insert into tab1 values(101,5);
create table tab2(stock number, slip number);
insert into tab2 values(4, 1);
insert into tab2 values(4, 5);
insert into tab2 values(4, 8);
insert into tab2 values(5, 1);
insert into tab2 values(5, 5);
insert into tab2 values(5, 6);
insert into tab2 values(5, 7);
commit;
desired results horse stock slip
100 4 8
101 5 7
select t1.horse, t2.stock, t2.slip,
rank ()
over (partition by horse, slip
order by slip desc)
rnk
from tab1 t1
join tab2 t2
on t1.stock = t2.stock
where rnk = 1
returns error: "rnk" invalid identifier....all pointers & suggestions appreciated...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER