Solved

Oracle Group query

Posted on 2013-01-04
5
357 Views
Last Modified: 2013-01-07
I have the following query

select
LOOKUP_SUB_TYPE_CODE as PERMIT_ID,
LOOKUP_VALUE_TEXT as NET_ID,
RTRIM(XMLAGG(XMLELEMENT(e, LOOKUP_MISC_CODE || '|')).extract ('//text()'), '|') as PrintDates
FROM
OMNI_COMMON_LOOKUP WHERE LOOKUP_KEY='TPSA_PRINT_LOG'
group by LOOKUP_SUB_TYPE_CODE, LOOKUP_VALUE_TEXT

If there are multiple PermitIDs and the same NET_IDs it Groups them and then it will Pipe delimit the dates for the PrintDates field and it works good. I do however wanna have the PrintDates ordered Descending. I do not have to use the current logic if I cant get them in descending order.

Here is an example:
Permit_ID     Net_ID       PrintDates
100047          XYZ           08/08/2012 11:57:01 AM|08/08/2012 11:58:44 AM
                                          (I need this to order the dates desc)
Thanks
0
Comment
Question by:jknj72
  • 3
  • 2
5 Comments
 

Author Comment

by:jknj72
ID: 38745177
FYI, my PrintDate is a text field so it will have to be converted to dates
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38745193
just add an order by to the xmlagg call


SELECT lookup_sub_type_code AS permit_id,
       lookup_value_text AS net_id,
       RTRIM(
           XMLAGG(XMLELEMENT(e, lookup_misc_code || '|') ORDER BY
                                                             TO_DATE(
                                                                 lookup_misc_code,
                                                                 'mm/dd/yyyy hh:mi:ss am'
                                                             )).EXTRACT('//text()'),
           '|'
       )
           AS printdates
  FROM omni_common_lookup
 WHERE lookup_key = 'TPSA_PRINT_LOG'
GROUP BY lookup_sub_type_code, lookup_value_text
0
 

Author Comment

by:jknj72
ID: 38745289
Where would I put the DESC at?
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 38745359
SELECT lookup_sub_type_code AS permit_id,
       lookup_value_text AS net_id,
       RTRIM(
           XMLAGG(XMLELEMENT(e, lookup_misc_code || '|') ORDER BY
                                                             TO_DATE(
                                                                 lookup_misc_code,
                                                                 'mm/dd/yyyy hh:mi:ss am'
                                                             ) DESC).EXTRACT('//text()'),
           '|'
       )
           AS printdates
  FROM omni_common_lookup
 WHERE lookup_key = 'TPSA_PRINT_LOG'
GROUP BY lookup_sub_type_code, lookup_value_text
0
 

Author Closing Comment

by:jknj72
ID: 38750604
Thank you
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Strange msg in the SSMS pane 13 47
Query Records that don't match 8 32
Queries 15 34
Using OPENQUERY in a SELECT statement 6 31
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.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

947 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now