Composite Key and Group By Issue 3

[Foada]

Thanks in advance for any insight on this one. I have a composite key that is formed by a customer id and an address sequence number. Each time a address sequence is added to a customer a new Address id is added. If I perform a Max() on the sequence number and group by the customer id I get the correct rows back but I cannot pull the address id as it is not an aggregate or in the group by.

CustId SeqNum AddressId
1000 1 20001
1000 2 23409
1000 3 24706
1000 4 25103

Basically I need to pull the AddressId of 25103 as it is the highest sequence number for customer 1000.

Select CustId, Max(SeqNum) from tblCust Group By CustId

Returns my CustId of 1000 and SeqNum of 4 but I need the AddressId. Any thoughts?

If you choose to battle wits with the witless be prepared to lose.

 

[markros]

Please read this blog exploring this problem in details

http://blogs.lessthandot.com/index....rver/including-an-aggregated-column-s-related

 

[jbenson001]

Try:

select A.*
from YourTable A
inner join
(    select CustID, Max(SeqNum) as maxSeqNum
    from YourTable
    group by CustID
) B on A.CustID=B.CustID and A.SeqNum=B.maxSeqNum

 

[bborissov]

Are your address IDs can be smaller when SeqNum is bigger?
In other words can you have something like this:
[tt]
CustId SeqNum AddressId
1000 1 28912
1000 2 25103
1000 3 24706
1000 4 23409
[/tt]
?

If yes, you could use a derived table to get what you want:

SELECT YourTable.*
FROM YourTable
INNER JOIN (SELECT CustId, MAX(SeqNum) AS SeqNum
                   FROM YourTable
            GROUP BY CustId) Tbl1
      ON YourTable.CustId = Tbl1.CustId AND
         YourTable.SeqNum = Tbl1.SeqNum

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Foada]

Thanks everyone. I knew it would be something easy, I just could not get it figured the way I needed to.

If you choose to battle wits with the witless be prepared to lose.