• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

Oracle Group query

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
jknj72
Asked:
jknj72
  • 3
  • 2
1 Solution
 
jknj72Author Commented:
FYI, my PrintDate is a text field so it will have to be converted to dates
0
 
sdstuberCommented:
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
 
jknj72Author Commented:
Where would I put the DESC at?
0
 
sdstuberCommented:
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
 
jknj72Author Commented:
Thank you
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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