Re: Aggregate Fun (Getting the Max out of MAX)...head scratcher
- From: sean.pinto@xxxxxxxxx
- Date: Wed, 07 Nov 2007 17:41:36 -0000
On Nov 6, 7:37 pm, "Plamen Ratchev" <Pla...@xxxxxxxxxxxxx> 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
.
- References:
- Aggregate Fun (Getting the Max out of MAX)...head scratcher
- From: sean . pinto
- Re: Aggregate Fun (Getting the Max out of MAX)...head scratcher
- From: Plamen Ratchev
- Aggregate Fun (Getting the Max out of MAX)...head scratcher
- Prev by Date: Using variable in Stored procedure - help!
- Next by Date: SQL 2005 32bit upgrade to 64bit possible?
- Previous by thread: Re: Aggregate Fun (Getting the Max out of MAX)...head scratcher
- Next by thread: Re: Aggregate Fun (Getting the Max out of MAX)...head scratcher
- Index(es):
Relevant Pages
|
Loading
