Return first occurance of record

[j420exe]

I would like to return the first occurance of a record. The first occurance is date driven. Searched on message boards and internet and saw a few different ways to tackle this. Is using the ROWNUM = 1 the way to do it? If not, what is the recommendation solution?

SELECT 'BILLING' as "Repository", o.ORDER_ID as "Order ID", O.ORDER_DATE as "Date Ordered", s.SHIP_DATE as "Date Shipped", (s.SHIP_DATE-O.ORDER_DATE) as "Lag Time"
FROM BILLING_DB.ORDERS o, BILLING_DB.SHIPPING s 
WHERE o.ORDER_ID = s.SHIP_ORDER_ID
AND o.ORDER_ID = 4043

Returns this data set:

Repository Order ID Date Ordered Date Shipped Lag Time
BILLING 4043 12/01/2008 00:00:00 12/01/2008 00:00:00 0
BILLING 4043 12/01/2008 00:00:00 12/02/2008 00:00:00 1

But I only want it to return the first occurrence (the first time the order was placed/shipped).

Repository Order ID Date Ordered Date Shipped Lag Time
BILLING 4043 12/01/2008 00:00:00 12/01/2008 00:00:00 0

I tried using min(s.SHIP_DATE) but that still returned the same result set.

Any help is very appreciated.
Thanks.

[amitpatel66]

You can use your lag_time column here:

 
SELECT repository,order_id,date_ordered,date_shipped,lag_time FROM
(SELECT Repository,order_id,date_ordered,date_shipped,lag_time,row_number() OVER(PARTITION BY order_id ORDER BY lag_time) rn FROM
(SELECT 'BILLING' as Repository, o.ORDER_ID as Order_ID, O.ORDER_DATE as Date_Ordered, s.SHIP_DATE as Date_Shipped, (s.SHIP_DATE-O.ORDER_DATE) as Lag_Time 
FROM BILLING_DB.ORDERS o, BILLING_DB.SHIPPING s  
WHERE o.ORDER_ID = s.SHIP_ORDER_ID 
AND o.ORDER_ID = 4043)) WHERE rn = 1
 
 

[Pilgrim333]

Hi,

The easiest way to do this:

SELECT 'BILLING' as "Repository"
     , o.ORDER_ID as "Order ID"
     , O.ORDER_DATE as "Date Ordered"
     , s.SHIP_DATE as "Date Shipped"
     , (s.SHIP_DATE-O.ORDER_DATE) as "Lag Time" 
FROM BILLING_DB.ORDERS o
   , BILLING_DB.SHIPPING s  
WHERE o.ORDER_ID = s.SHIP_ORDER_ID 
AND   o.ORDER_ID = 4043 
AND   rownum = 1
order by s.SHIP_DATE
 

If it is not necessary to use select statements in the from clause then don't do it. It makes things too complicated and not harder readible for other programmers. As this has a simple and readible solution, my personal preference goes to that one.

Pilgrim.

[amitpatel66]

it has to be for multiple order_id and not just one. Remove the condition of order_id and the query with rownum will not work

@OP,

There are many other solutions for your issue and the one that I provided is one way of doing it. There is nothing so COMPLEX in that query and it uses a concept of INLIVE VIEW that is supported by ORACLE which is really very helpful in achieving what we require.

You can remove the ORDER_ID = 4043 condition from the query that I provided to make it work for all the ORDER_ID's

[amitpatel66]

@OP, Please test the query and let us know the results for multiple order_id

[Pilgrim333]

I did not get it that it should be for multiple id's. But if that is the issue, then my personal preference would still go to the following code

SELECT 'BILLING' as "Repository" 
     , o.ORDER_ID as "Order ID" 
     , O.ORDER_DATE as "Date Ordered" 
     , s.SHIP_DATE as "Date Shipped" 
     , (s.SHIP_DATE-O.ORDER_DATE) as "Lag Time"  
FROM BILLING_DB.ORDERS o 
   , BILLING_DB.SHIPPING s   
WHERE o.ORDER_ID = s.SHIP_ORDER_ID  
AND   s.SHIP_DATE =
      (select min(t.shipdate)
       from  BILLING_DB.SHIPPING t
       where t.ship_order_id = o.order_id
      )
 

It is much more readible and explainotry, another programmer (or even you after a while) will see in a glimpse what the query will return.

Pilgrim.

[j420exe]

Both queries returned the same result set and I learned something new.

I will have to read up on the Oracle OVER (PARITION ..) keywords and the row_number() method and how it's used/processed in oracle-sql.

row_number() OVER(PARTITION BY TEST_ID ORDER BY lag_time) rn
 

Thanks again!!!

[Pilgrim333]

Good to hear your problem is solved. The use of either queries is depending on your personal preference.

Good luck with the rest of your application.

Pilgrim.