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

This worked for me:

create table loyd/dan
(customer char(10) not null,
renewal int,
sequence int)
Table DAN in LOYD created but was not journaled.

insert into loyd/dan (customer, renewal)
values ('3441978',3)

insert into loyd/dan (customer, renewal)
values ('3441978',4)

insert into loyd/dan (customer, renewal)
values ('3452173',3)

insert into loyd/dan (customer, renewal)
values ('3470317',3)

insert into loyd/dan (customer, renewal, sequence)
values ('3441978',4,1)

insert into loyd/dan (customer, renewal, sequence)
values ('3452173',3,1)

insert into loyd/dan (customer, renewal, sequence)
values ('3452173',3,2)

insert into loyd/dan (customer, renewal, sequence)
values ('3470317',3,1)

select * from loyd/dan order by 1,2,3

....+....1....+....2....+....3....+....4..
CUSTOMER RENEWAL SEQUENCE
3441978 3 -
3441978 4 1
3441978 4 -
3452173 3 1
3452173 3 2
3452173 3 -
3470317 3 1
3470317 3 -
******** End of data ********

select * from loyd/dan a
where a.renewal = (select max(z.renewal) from loyd/dan z
where a.customer=z.customer)
and a.sequence = (select max(coalesce(z.sequence,0))
from loyd/dan z where a.customer=z.customer
and a.renewal=z.renewal)

....+....1....+....2....+....3....+....4..
CUSTOMER RENEWAL SEQUENCE
3441978 4 1
3452173 3 2
3470317 3 1
******** End of data ********

If your sequence column is not null, you can forgo the coalesce in the
subselects.

The subselects are processed in the order listed. First, we find the
maximum renewal for each customer, then find the maximum sequence for
the customer/renewal combination.

On tables with lots of rows, this can take a while to run if you don't
have proper indexes in place.

HTH,
Loyd


Loyd Goodbar
Senior programmer/analyst
BorgWarner
TS Water Valley
662-473-5713
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan
Sent: Tuesday, July 31, 2007 09:43
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