SQL Query for Data model

Question

Summary

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

Smanikandan65 · Answer

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 , row_number() over(partition by SHIPMENT_GID, S_SHIP_UNIT_GID order by desc or asc ) rown FROM shipment_stop_debrief WHERE SHIPMENT_GID = 'PEI.H171123883' ORDER BY INSERT_DATE DESC) where rown=1

Antoine KAIBER · Answer

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;