Mis-typed what we wanted.....meant we wanted rec #1, #2, #4, #5, #6, and
#7 - skipping #3 and #8



Does the below still hold true?



Basically, it's get the record if we haven't had the col1/col2 pair
before.



pbt.







-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
Sent: Wednesday, April 11, 2007 7:52 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: data retrieval



--- In firebird-support@yahoogroups.com
<mailto:firebird-support%40yahoogroups.com> , "p_b_turner"
<PeterTurner@...> wrote:

>
> hi,
>
> have a table with the following conceptual data...
> rec# col1 col2 ... coln
> 1 1 d data
> 2 1 m data
> 3 1 d data
> 4 2 d data
> 5 2 m data
> 6 3 d data
> 7 3 m data
> 8 3 d data
>
> trying to come up with a single query that would return unique records

> where the combination of col1 and col2 are unique. in the example,
> would want rec #1, #2, #3, #4, #6, and #7.
>
> have come up with a couple of multiple query ideas, but was wondering
> how to do this in a single query (if possible).

You could do it with a not exists clause:

select m1.*
from MyTable m1
where Not Exists
(
select 1
from MyTable m2
where m1.Col1 = m2.Col1
and m1.Col2 = m2.Col2
and m1.RecNo <> m2.RecNo
)

Make sure Col1 and Col2 are indexed (in this case a compound index
would be equally useful and probably cheaper from a garbage collection
point of view).

That will get it down to a plan something like

PLAN (M2 INDEX (IX_COL1COL2))
PLAN (M1 NATURAL)

So a natural scan on MyTable and an indexed lookup for each record,
not too bad.

Adam





[Non-text portions of this message have been removed]