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
Solved

oracle sql analytics query

Posted on 2014-10-01
3
130 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
3 Comments
 
LVL 76

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

809 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