>Hi EveryOne,
>
>I have a table like this and wanna to distinct some columns so that only one
>record will show for this criteria
>(User1 <> User2 AND User1SampleNo <> User2SampleNo)
>
>
>
>User1       User1SampleNo User2       User2SampleNo EuclideanDistance
>----------- ------------- ----------- -------------
>---------------------------------------
>1           1             1           1             0.0000000
>1           1             1           2             1.6366812
>1           1             1           3             3.7507731
>1           1             1           4             2.5621797
>1           1             1           5             2.5280885
>1           2             1           1             1.6366812
>1           2             1           2             0.0000000
>1           2             1           3             2.9923197
>1           2             1           4             2.1335549
>1           2             1           5             2.4842911
>
>
>Any hints?
>
>Thx for your kindest help
>Norton
>

> >I have a table like this and wanna to distinct some columns so that only
> >one
>>record will show for this criteria
>>(User1 <> User2 AND User1SampleNo <> User2SampleNo)
> In the sample data provided User1 = User2 for all rows, so NO rows
> satisfy the criteria.  Since you mention DISTINCT, perhaps you could
> write a query to demonstrate what you mean?  One using DISTINCT or
> GROUP BY?
>
> You have to provide a rule for which value of EuclideanDistance you
> want returned.  The smallest (MIN) or the greatest (MAX) are the
> easiest.  No, "it does not matter" is not an acceptable answer.
> Neither is "the first one" or "the last one".
>
> In general, most such problems end up using one of a few approaches, b
>
> SELECT User1, User1SampleNo,
>       User2, User2SampleNo,
>       MAX(EuclideanDistance) as EuclideanDistance
>  FROM Whatever
> GROUP BY User1, User1SampleNo,
>       User2, User2SampleNo
>
> SELECT *
>  FROM Whatever as A
> WHERE NOT EXISTS
>       (select * from Whatever as B
>         where A.User1 = B.User1
>           and A.User2 = B.User2
>           and A.User1SampleNo = B.User1SampleNo
>           and A.User2SampleNo = B.User2SampleNo
>           and A.EuclideanDistance < B.EuclideanDistance)
>
> SELECT *
>  FROM Whatever as A
> WHERE EuclideanDistance =
>       (select MAX(EuclideanDistance)
>          from Whatever as B
>         where A.User1 = B.User1
>           and A.User2 = B.User2
>           and A.User1SampleNo = B.User1SampleNo
>           and A.User2SampleNo = B.User2SampleNo)
>
> Roy Harvey
> Beacon Falls, CT
>
>
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 22 Jun 2006 10:14:02 -0700, Norton
> <Nor***@discussions.microsoft.com> wrote:
>
>>Hi EveryOne,
>>
>>I have a table like this and wanna to distinct some columns so that only
>>one
>>record will show for this criteria
>>(User1 <> User2 AND User1SampleNo <> User2SampleNo)
>>
>>
>>
>>User1       User1SampleNo User2       User2SampleNo EuclideanDistance
>>----------- ------------- ----------- -------------
>>---------------------------------------
>>1           1             1           1             0.0000000
>>1           1             1           2             1.6366812
>>1           1             1           3             3.7507731
>>1           1             1           4             2.5621797
>>1           1             1           5             2.5280885
>>1           2             1           1             1.6366812
>>1           2             1           2             0.0000000
>>1           2             1           3             2.9923197
>>1           2             1           4             2.1335549
>>1           2             1           5             2.4842911
>>
>>
>>Any hints?
>>
>>Thx for your kindest help
>>Norton
>>

On Fri, 23 Jun 2006 01:58:08 +0800, "Norton" <NortonW***@hotmail.com>
wrote:

>Thx for your hlep, I would like to have the following outcomes after running
>the sql query
>
>User1       User1SampleNo User2       User2SampleNo EuclideanDistance
>----------- ------------- ----------- -------------
>---------------------------------------
>1           1             1           1             0.0000000
>1           1             1           2             1.6366812
>1           1             1           3             3.7507731
>1           1             1           4             2.5621797
>1           1             1           5             2.5280885
>1           2             1           1             1.6366812  <---Remove
>1           2             1           2             0.0000000
>1           2             1           3             2.9923197
>1           2             1           4             2.1335549
>1           2             1           5             2.4842911
>
>
>For all resulting recordset, i would like to ensure all (User1 AND
>User1SampleNo) won't be equal to (User2 AND User2SampleNo)
>
>
>Thx a lot
>
>
>
>"Roy Harvey" <roy_har***@snet.net>
>???????:pskl92d11a0ibnsu5j2ajtu7u026ajk***@4ax.com...
>> >I have a table like this and wanna to distinct some columns so that only
>> >one
>>>record will show for this criteria
>>>(User1 <> User2 AND User1SampleNo <> User2SampleNo)
>> In the sample data provided User1 = User2 for all rows, so NO rows
>> satisfy the criteria.  Since you mention DISTINCT, perhaps you could
>> write a query to demonstrate what you mean?  One using DISTINCT or
>> GROUP BY?
>>
>> You have to provide a rule for which value of EuclideanDistance you
>> want returned.  The smallest (MIN) or the greatest (MAX) are the
>> easiest.  No, "it does not matter" is not an acceptable answer.
>> Neither is "the first one" or "the last one".
>>
>> In general, most such problems end up using one of a few approaches, b
>>
>> SELECT User1, User1SampleNo,
>>       User2, User2SampleNo,
>>       MAX(EuclideanDistance) as EuclideanDistance
>>  FROM Whatever
>> GROUP BY User1, User1SampleNo,
>>       User2, User2SampleNo
>>
>> SELECT *
>>  FROM Whatever as A
>> WHERE NOT EXISTS
>>       (select * from Whatever as B
>>         where A.User1 = B.User1
>>           and A.User2 = B.User2
>>           and A.User1SampleNo = B.User1SampleNo
>>           and A.User2SampleNo = B.User2SampleNo
>>           and A.EuclideanDistance < B.EuclideanDistance)
>>
>> SELECT *
>>  FROM Whatever as A
>> WHERE EuclideanDistance =
>>       (select MAX(EuclideanDistance)
>>          from Whatever as B
>>         where A.User1 = B.User1
>>           and A.User2 = B.User2
>>           and A.User1SampleNo = B.User1SampleNo
>>           and A.User2SampleNo = B.User2SampleNo)
>>
>> Roy Harvey
>> Beacon Falls, CT
>>
>>
>> Roy Harvey
>> Beacon Falls, CT
>>
>> On Thu, 22 Jun 2006 10:14:02 -0700, Norton
>> <Nor***@discussions.microsoft.com> wrote:
>>
>>>Hi EveryOne,
>>>
>>>I have a table like this and wanna to distinct some columns so that only
>>>one
>>>record will show for this criteria
>>>(User1 <> User2 AND User1SampleNo <> User2SampleNo)
>>>
>>>
>>>
>>>User1       User1SampleNo User2       User2SampleNo EuclideanDistance
>>>----------- ------------- ----------- -------------
>>>---------------------------------------
>>>1           1             1           1             0.0000000
>>>1           1             1           2             1.6366812
>>>1           1             1           3             3.7507731
>>>1           1             1           4             2.5621797
>>>1           1             1           5             2.5280885
>>>1           2             1           1             1.6366812
>>>1           2             1           2             0.0000000
>>>1           2             1           3             2.9923197
>>>1           2             1           4             2.1335549
>>>1           2             1           5             2.4842911
>>>
>>>
>>>Any hints?
>>>
>>>Thx for your kindest help
>>>Norton
>>>
>