Aggregate Fun (Getting the Max out of MAX)...head scratcher

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

Re: Aggregate Fun (Getting the Max out of MAX)...head scratcher

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

Re: Aggregate Fun (Getting the Max out of MAX)...head scratcher

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))
)

Re: Aggregate Fun (Getting the Max out of MAX)...head scratcher

On Nov 6, 7:37 pm, "Plamen Ratchev" 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