Solved

oracle sql analytics query

Posted on 2014-10-01
3
129 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

Suggested Solutions

Title # Comments Views Activity
SQL Server - Replace Carriage Returns for Excel Exports 17 54
SQL NULL vs Blank 26 36
Problem with duplicate records in Oracle query 16 30
Sql Server group by 10 30
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

823 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