Thanks to all for the suggestions. Have passed them on to the
developer. Will update the thread with his results.



pbt.



-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling
Tysvaer
Sent: Thursday, April 12, 2007 9:39 AM
To: Firebird-support
Subject: Re: [firebird-support] Re: data retrieval



Yes Peter, virtually - just change '<>' to '>' to not eliminate the
first occurence in case of duplicates:

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
)

HTH,
Set

Peter Turner wrote:

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