select e.emp,e.empname,ek.empid,ek.systemkey,ek,systemid , ROW_NUMBER ( ) OVER ( PARTITION BY e.empid ORDER BY e.empid,ex.systemkey) joined_sequence from employee e inner join emp_key e on e.empid = ek.empid;
try this. On Fri, Dec 12, 2008 at 2:28 AM, BigRidge <[email protected]> wrote: > > Hi all. > > I have something like: > > select * > from employee e > inner join emp_key e > on e.empid = ek.empid > > which returns something like: > > e.empid, e.empname, ek.empid, ek.systemkey, ek.systemid > 1 ,John ,1 ,1234 ,SAP > 2 ,Peter ,2 ,2345 ,SAP > 2 ,Peter ,2 , > 3456 ,GreatPlains > 2 ,Peter ,2 ,4567 ,Genesys > 3 .Philip ,3 ,5678 ,SAP > 3 .Philip ,3 , > 6789 ,GreatPlains > > > What I really want is a sequence indicating the row number for each > joined results so I get somethink like this: > > e.empid, e.empname, ek.empid, ek.systemkey, ek.systemid, > joined_sequence > 1 ,John ,1 , > 1234 ,SAP ,1 > 2 ,Peter ,2 , > 2345 ,SAP ,1 > 2 ,Peter ,2 , > 3456 ,GreatPlains ,2 > 2 ,Peter ,2 , > 4567 ,Genesys ,3 > 3 .Philip ,3 , > 5678 ,SAP ,1 > 3 .Philip ,3 , > 6789 ,GreatPlains ,2 > > Does anybody have an idea how to do this? > > Regards, > Matthew > > > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
