RE: SQL question: Select all unique Key1, but only last of Key2/Key3

How about:

With
Slt1 (Cust, Ren#) as
( Select a1.Customer, max(a1.Renewal)
From RenewalDB a1
Group By a1.Customer),
Slt2 (Cust, Ren#, Seq#) as
( Select a2.Customer, a2.Renewal, max(a2.Sequence)
From RenewalDB a2 inner join
Slt1 on (a2.Customer=Slt1.Cust and
a2.Renewal =Slt1.Ren#)
Group By a2.Customer, a2.Renewal)

Select a.*

From RenewalDB a inner join

Slt2 on (a.Customer=Slt2.Cust and
a.Renewal =Slt2.Ren# and
a.Sequence=Slt2.Seq#)

hth,
Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Dan
Sent: Tuesday, July 31, 2007 9:43 AM
To: Midrange Systems Technical Discussion
Subject: SQL question: Select all unique Key1, but only last of
Key2/Key3


Have a policy detail file keyed by customer#, renewal#, sequence#. I need
to select all customers, but only the last one in key sequence for each
unique customer. Renewal# and sequence# are 2-digit numerics. Am I looking
to use DISTINCT with a subselect? My attempts so far have netted only
syntax errors.

Customer# Renewal# Sequence#
3441978 3
3441978 4
3441978 4 1 <== select this one
3452173 3
3452173 3 1
3452173 3 2 <== select this one
3470317 3
3470317 3 1 <== select this one

TIA,
Dan