Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with unique rows

Status
Not open for further replies.

DomCotton

Programmer
Feb 19, 2004
4
GB
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
 
SELECT customer_id, max(valid_from)
GROUP BY customer_id;

should work. What is wrong with it?

Elbert, CO
0912 MST
 
Try this.
select *
from tbl_nm a
where a.valid_from = (select max(valid_from) from tbl_nm b
where b.customer_id = a.customer_id)
 
Hi,

Thanks for the help. I had tried using a subquery - the problem was the resultset was way to slow. I have now managed it using something similar to carp's pointers.

Thanks for the help.

Dom
 
Please post the solution so that others may learn!

 
Hi,

Apologies!

I did the following (my simple example was so that I didn't have to write loads of columns, etc...).

select customer_id, {all other fields}, max(valid_from) from (
select customer_id, {all other fields}, valid_from from table where ( all exclusions, etc )
) group by customer_id, {all other fields}

The reason I had to do this is because all of my other fields were function calls. Without the sub-query the select on this view took so long.... doing the Max() on a result of the sub-query seemed much quicker.

It's probable a terrible way to do things but it worked great for me!

Dom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top