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)

With an ascending index on RXCLAIMNBR, this is the plan that is
generated and the query runs OK:
PLAN SORT ((T2 INDEX (RXCLAIMNBR)))
PLAN (T1 NATURAL)

With an additional descending index on CLMSEQNBR, this is the plan that
is generated, and the query doesn't return and the CPU is pegged:
PLAN (T2 ORDER CLMSEQNBR)
PLAN (T1 NATURAL)

Adding an index that contains both columns is never used by the plan.
What do I need to do to improve the plan?

Thanks, Rick DeBay

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