Thanks Alexander. One last question. I've expanded the select to using
three columns, and now this is what it looks like. I'm sure it's
obvious that I'm not a DBA.

Select *
From CLAIMSPAIDREVERSED T1
Where T1.CLMSEQNBR=
(Select First 1 T2.CLMSEQNBR
From CLAIMSPAIDREVERSED T2
Where T2.RXCLAIMNBR=T1.RXCLAIMNBR
Order By T2.CLMSEQNBR DESC)
AND
T1.CLAIMSTS=
(Select First 1 T3.CLAIMSTS
From CLAIMSPAIDREVERSED T3
Where T3.RXCLAIMNBR=T1.RXCLAIMNBR AND T3.CLMSEQNBR=T1.CLMSEQNBR
Order By T3.CLAIMSTS)

PLAN SORT ((T2 INDEX (CLAIMNUMBER)))
PLAN SORT ((T3 INDEX (CLAIMNUMBER)))
PLAN (T1 NATURAL)

What I'm trying to do is get the last item in a sequence, where column 1
counts up, column 2 down, and column 3 up. So a unique key would
combine all three columns:

001 999 P
001 999 X
001 998 P

What I created from Alexander's answer works, but it could probably be
better.

-----Original Message-----
From: Alexander V.Nevsky [mailto:ded@...]
Sent: Friday, July 09, 2004 3:19 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Select question

--- In firebird-support@yahoogroups.com, "Rick DeBay" <rdebay@r...>
wrote:

> Given a table
>
> name sequence otherColumns...
> A 999
> A 998
> B 999
> B 998
> B 997
> C 999
>
> how can I get back only rows two five and six, plus their associated
> other columns?
> My immediate thought was to use GROUP BY and get the MIN of sequence,
> but of course that wouldn't give me the other columns of that row.

Select T1.*
From ThisTable T1
Where T1.Sequence=
(Select First 1 T2.Sequence
From ThisTable T2
Where T2.Name=T1.Name
Order By T2.Sequence)

Best regards,
Alexander.





Yahoo! Groups Links