Query Help [message #275517] |
Sun, 21 October 2007 00:31  |
sumachaa
Messages: 1 Registered: October 2007
|
Junior Member |
|
|
Hi,
im new to oracle. please help me with the below query
Below mentioned table to store all the change log information regarding an order for a all customers.
i.e what value was changed for that field and to what value its changed, the datetime when in happened
CUSTOMER_ID VARCHAR
ORDER_NO VARCHAR
FIELD_CHANGED VARCHAR
CHANGED_ON DATE_TIME
OLD_VALUE VARCHAR
NEW_VALUE VARCHAR
This table will store all values changed for a particular field, i.e for an order, for a particular field how many times the value was changed,
CUSTOMER_ID ORDER_NO FIELD_CHANGED CHANGED_ON OLD_VALUE NEW_VALUE
ABC 100 PURCHASE_TYPE 2007-01-01 10:43:12 DEV DEV_TEST
ABC 100 ORDER_GRP 2007-02-03 23:12:12 SALES PREPAID
ABC 100 PURCHASE_TYPE 2006-02-02 12:12:12 COLD DEV
EFY -- --- --- --- ---
--- --- --- -- --- ---
Now the help I need is to get the values changed for a particular customer, for all orders, for all field the last value only (which we can know from the changed_on field)
i.e for a particular order there can be many rows for the same field, I may have changed it many times, but I need only the last one
as from the example we can see that PURCHASE_TYPE field was changed 2 times, for the same order. But my query should return the last one only
the database used is oracle 10g.
let me know if u have any more questions.
thanks
Sumesh
|
|
|
Re: Query Help [message #275518 is a reply to message #275517] |
Sun, 21 October 2007 00:55  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
What you want is the first row if you partition the table rows by customer_id, order_no and field_changed and order these partitions by changed_on date in descending order.
The ROW_NUMBER, RANK or DENSE_RANK functions will help you.
By the way, read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Regards
Michel
|
|
|