Posted by sean.pinto on 11/07/07 00:56

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


Now initially I was doing something like the following under the
assumption that the key_no's would be auto-incremented so the largest
key_no was the most recent row.

SELECT *
FROM tx_cust_memb cm
JOIN
(SELECT MAX(key_no) AS key_no
FROM tx_cust_memb cmInner
GROUP BY customer_no) derived
ON derived.key_no = cm.key_no


However, what if someone updated the inception date of a row due to a
mistake? I am looking for a basically a way to do grab the TOP 1 key
of a grouping ordering by another column. And do so in a concise and
clean way. I feel like there is a way to do it with the ROW_NUMBER()
OVER clause but don't know how. Please help!

Thanks,
Sean

[Back to original message]