Solved

oracle sql analytics query

Posted on 2014-10-01
3
132 Views
Last Modified: 2014-10-30
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
Comment
Question by:tonMachine100
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 167 total points
ID: 40354630
Try this for new_field:
dense_rank() over(order by datagroupname desc) new_field
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 167 total points
ID: 40354939
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 166 total points
ID: 40372997
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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question