oracle sql analytics query

please see the attached which shows output from table1 (select * from table1), and desired output.

I need some help to get new_field. This field is the order of the datagroup name based on the relative_order.

Any help is appreciated.
ee-example.xlsx
tonMachine100Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Try this for new_field:
dense_rank() over(order by datagroupname desc) new_field
0
MikeOM_DBACommented:
Or perhaps:
SQL> l
  1  WITH Table1 ( Form_Instance_Id, Datagroupname, Relative_Order )
  2       AS (SELECT 1, 'Outcome Number', 4 FROM DUAL UNION ALL
  3           SELECT 1, 'Outcome Number', 4 FROM DUAL UNION ALL
  4           SELECT 1, 'Personal Budget Value', 3 FROM DUAL UNION ALL
  5           SELECT 1, 'Personal Budget Value', 3 FROM DUAL UNION ALL
  6           SELECT 1, 'Provision', 1 FROM DUAL UNION ALL
  7           SELECT 1, 'Provision', 1 FROM DUAL)
  8  SELECT X.*, DENSE_RANK ( ) OVER (ORDER BY Relative_Order) New_Value
  9    FROM Table1 X
 10*  ORDER BY Datagroupname
SQL> /

FORM_INSTANCE_ID DATAGROUPNAME                    RELATIVE_ORDER  NEW_VALUE
---------------- -------------------------------- -------------- ----------
               1 Outcome Number                                4          3
               1 Outcome Number                                4          3
               1 Personal Budget Value                         3          2
               1 Personal Budget Value                         3          2
               1 Provision                                     1          1
               1 Provision                                     1          1

6 rows selected.

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
Slight variation -
select form_instance_id, datagroupname, relative_order,
dense_rank() over (order by relative_order) new_field
from report_title
order by datagroupname;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.