--- In firebird-support@yahoogroups.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