Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

SQL Query for Data model

Received Response
21
Views
2
Comments
Antoine KAIBER
Antoine KAIBER Rank 6 - Analytics Lead
in Oracle Analytics Cloud and Server

Summary

SQL Query for Data model

Content

Hello,

I'm working on an RTF template on which I want to display certain information of a table called "shipment_stop_debrief". In this table figures serveral “S_SHIP_UNIT_GID’s" with different numbers.  I want the quantity of "ITEM_PACKAGE_COUNT" and "TRANSPORT_HANDLING_UNIT_COUNT" for the last "S_SHIP_UNIT_GID" of a certain type.

When I say last of a certain type, I mean that a “S_SHIP_UNIT_GID" can be updated and in some cases and it will create another “S_SHIP_UNIT_GID" with the same reference number, but I only need the most recent (lastly updated) “S_SHIP_UNIT_GID" of a same reference number.

Example in the screen below, which shows two same ship_unit_gid’s with different quantities, I want to select the one that is most recent which corresponds to the one with a quantity of 13 :

The SQL that corresponds to it is this one:

SELECT *

FROM

  (SELECT *

FROM shipment_stop_debrief

WHERE SHIPMENT_GID = 'PEI.H171123883'

ORDER BY INSERT_DATE DESC)

So ship_unit_gid’s are ordered by date and I would like to select PEI.205165, PEI.205166, PEI.205164 (with the quantity of 13).

Do you guys have any idea ?

Thanks,

Antoine

Answers

  • Smanikandan65
    Smanikandan65 Rank 5 - Community Champion

    Hi Antonie - you would need to use rank() / row_number() with partitions to get the row you want.

    Find out which field can be considered to identify whether this record is recent or not.

    Based on this field, you can do .. See example below

    SELECT *

    FROM

      (SELECT <columns>,

                      row_number() over(partition by SHIPMENT_GID,  S_SHIP_UNIT_GID order by <your field that decides whether its recent or not> desc or asc <depends on your requirement>) rown

    FROM shipment_stop_debrief

    WHERE SHIPMENT_GID = 'PEI.H171123883'

    ORDER BY INSERT_DATE DESC)

    where rown=1

  • Antoine KAIBER
    Antoine KAIBER Rank 6 - Analytics Lead

    Thanks for your answer, I forgot to post here but I already got my answer on the SQL forum of oracle. The SQL query is:

    SELECT * FROM

    (

    select t.*, row_number() over (partition by s_ship_unit_gid  ORDER BY INSERT_DATE DESC) rn

    FROM shipment_stop_debrief t

    WHERE SHIPMENT_GID = 'PEI.H171123883'

    )

    where rn = 1;