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