469,338 Members | 8,430 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,338 developers. It's quick & easy.

Return first occurance of record

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?

Expand|Select|Wrap|Line Numbers
  1. 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"
  2. FROM BILLING_DB.ORDERS o, BILLING_DB.SHIPPING s 
  3. WHERE o.ORDER_ID = s.SHIP_ORDER_ID
  4. 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.
Dec 2 '08 #1
7 14295
amitpatel66
2,367 Expert 2GB
You can use your lag_time column here:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT repository,order_id,date_ordered,date_shipped,lag_time FROM
  3. (SELECT Repository,order_id,date_ordered,date_shipped,lag_time,row_number() OVER(PARTITION BY order_id ORDER BY lag_time) rn FROM
  4. (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 
  5. FROM BILLING_DB.ORDERS o, BILLING_DB.SHIPPING s  
  6. WHERE o.ORDER_ID = s.SHIP_ORDER_ID 
  7. AND o.ORDER_ID = 4043)) WHERE rn = 1
  8.  
  9.  
Dec 3 '08 #2
Pilgrim333
127 100+
Hi,

The easiest way to do this:

Expand|Select|Wrap|Line Numbers
  1. SELECT 'BILLING' as "Repository"
  2.      , o.ORDER_ID as "Order ID"
  3.      , O.ORDER_DATE as "Date Ordered"
  4.      , s.SHIP_DATE as "Date Shipped"
  5.      , (s.SHIP_DATE-O.ORDER_DATE) as "Lag Time" 
  6. FROM BILLING_DB.ORDERS o
  7.    , BILLING_DB.SHIPPING s  
  8. WHERE o.ORDER_ID = s.SHIP_ORDER_ID 
  9. AND   o.ORDER_ID = 4043 
  10. AND   rownum = 1
  11. order by s.SHIP_DATE
  12.  
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.
Dec 3 '08 #3
amitpatel66
2,367 Expert 2GB
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
Dec 3 '08 #4
amitpatel66
2,367 Expert 2GB
@OP, Please test the query and let us know the results for multiple order_id
Dec 3 '08 #5
Pilgrim333
127 100+
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

Expand|Select|Wrap|Line Numbers
  1. SELECT 'BILLING' as "Repository" 
  2.      , o.ORDER_ID as "Order ID" 
  3.      , O.ORDER_DATE as "Date Ordered" 
  4.      , s.SHIP_DATE as "Date Shipped" 
  5.      , (s.SHIP_DATE-O.ORDER_DATE) as "Lag Time"  
  6. FROM BILLING_DB.ORDERS o 
  7.    , BILLING_DB.SHIPPING s   
  8. WHERE o.ORDER_ID = s.SHIP_ORDER_ID  
  9. AND   s.SHIP_DATE =
  10.       (select min(t.shipdate)
  11.        from  BILLING_DB.SHIPPING t
  12.        where t.ship_order_id = o.order_id
  13.       )
  14.  
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.
Dec 3 '08 #6
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.

Expand|Select|Wrap|Line Numbers
  1. row_number() OVER(PARTITION BY TEST_ID ORDER BY lag_time) rn
  2.  
Thanks again!!!
Dec 3 '08 #7
Pilgrim333
127 100+
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.
Dec 3 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Andrew Poulos | last post: by
4 posts views Thread by Michael McCarthy | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.