Hi,
I am trying to construct a view in an Oracle database.
I have a select statement that returns something like below:
CUSTOMER_ID VALID_FROM
100 08/02/2004 00:01:11
100 08/02/2004 00:02:11
100 08/02/2004 00:03:11
101 08/02/2004 00:01:11
101 08/02/2004 00:02:11
101 08/02/2004 00:03:11
What is happening in the table is every time a customer is updated, it adds a new row to the same table (I have no control over the code that is creating the entries in this table) and changes the date. So, what I want is one row per customer, but I need the newest version. Select distinct wont work because the dates make each row unique.....
Does anybody know how I can achieve this? I have tried using Max(VALID_FROM) etc... in all of my queries but cannot get it to work!
Would be so grateful if somebody can help me...
Regards,
Dom
I am trying to construct a view in an Oracle database.
I have a select statement that returns something like below:
CUSTOMER_ID VALID_FROM
100 08/02/2004 00:01:11
100 08/02/2004 00:02:11
100 08/02/2004 00:03:11
101 08/02/2004 00:01:11
101 08/02/2004 00:02:11
101 08/02/2004 00:03:11
What is happening in the table is every time a customer is updated, it adds a new row to the same table (I have no control over the code that is creating the entries in this table) and changes the date. So, what I want is one row per customer, but I need the newest version. Select distinct wont work because the dates make each row unique.....
Does anybody know how I can achieve this? I have tried using Max(VALID_FROM) etc... in all of my queries but cannot get it to work!
Would be so grateful if somebody can help me...
Regards,
Dom