Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 136
  • Last Modified:

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
0
tonMachine100
Asked:
tonMachine100
3 Solutions
 
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
 
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now