|
|
Posted by Ed Murphy on 11/07/07 07:37
sean.pinto@gmail.com wrote:
> Ok, so I have had this problem more than once and can't think of a
> GOOD way to do it. Say I have a table with containing membership
> information (primary key, customer number, inception date,
> organization number). For each customer, they can have many
> memberships across different organizations as well as the same
> organization. What I want to know is how to get all the columns of
> the table ONCE for each customer but containing the information of the
> most recent row grouped by the customer.
>
> Example:
>
> key_no | customer_no | inception_date | org_no
> 1 1 1/1/2001 1
> 2 1 2/1/2000 2
> 3 2 3/13/2005 1
> 4 2 3/11/2005 1
> 5 2 12/12/2006 2
> 6 3 1/1/2001 3
> 7 4 2/2/2000 1
> 8 5 6/6/2006 4
> 9 5 7/23/2000 1
>
>
> Results:
>
> key_no | customer_no | inception_date | org_no
> 1 1 1/1/2001 1
> 5 2 12/12/2006 2
> 6 3 1/1/2001 3
> 8 5 6/6/2006 4
> 9 5 7/23/2000 1
select *
from the_table t1
where not exists (
select *
from the_table t2
where t2.customer_no = t1.customer_no
and (t2.inception_date > t1.inception_date
or (t2.inception_date = t1.inception_date
and t2.key_no > t1.key_no))
)
Navigation:
[Reply to this message]
|