Posted by sean.pinto on 11/07/07 17:41

On Nov 6, 7:37 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> 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 Ratchevhttp://www.SQLStudio.com

This is PERFECT! Thanks so much!

Sean

• Next in forum: Re: Tool for comparing result of 2 stored procs
• Prev in forum: Using variable in Stored procedure - help!
• Thread view: Re: Aggregate Fun (Getting the Max out of MAX)...head scratcher

[Reply to this message]