Posted by Plamen Ratchev on 11/07/07 03:37

Hi Sean,

Here is one way to do this (if I understand correctly your requirements) in
SQL Server 2005 using ROW_NUMBER. Based on your notes I assumed the latest
inception date can be used to identify the most recent update.

WITH cte
(key_no, customer_no, inception_date, org_no, seq_no)
AS
(
SELECT key_no, customer_no, inception_date, org_no,
ROW_NUMBER() OVER(
PARTITION BY customer_no
ORDER BY inception_date DESC,
key_no DESC) AS seq_no
FROM tx_cust_memb
)
SELECT key_no, customer_no, inception_date, org_no
FROM cte
WHERE seq_no = 1;

BTW, your results seem to be incorrect, you have customer 5 listed twice and
customer 4 is missing.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]