Hello everyone.
I have a complex situation. I have a list of related records (see
partial extract below) where I am trying to determine the unique lowest
number for each relation. This number should always be an AB number,
since all the MA numbers will always relate to an AB number. I need the
distinct, lowest values that relate to each other, not necessarily in
the same row.
What I have so far is:
SELECT distinct
if (test.Field1 < test.Field2,test.Field1,test.Field2)
AS `Unique Incident id`
FROM test
The problem is that the data is recursive and non-exclusive, by which I
mean that the data will always be in both columns, and it is possible
that more than one relation exists. In the case of more than 1 relation
I still need the just lowest distinct value in the relation, no matter
how many other members of the relation exist.
Can anyone offer any suggestions?
Here is some sample data to chew on.
AB100674,MA003296
AB100675,MA003306
AB100676,MA003314
AB100677,MA003312
AB100678,MA003321
AB100679,MA003318
AB100680,MA003320
AB100680,AB100687
AB100681,MA003335
AB100681,AB100687
AB100682,MA003330
AB100682,AB100687
AB100683,MA003337
AB100684,MA003336
AB100685,MA003332
AB100686,MA003341
AB100687,AB100633
AB100687,AB100661
AB100687,AB100680
AB100687,AB100681
AB100687,AB100682
AB100687,MA003343
AB100687,AB100688
AB100687,AB100690
AB100687,AB100722
AB100687,AB100731
AB100687,AB100792
AB100688,AB100687
AB100688,MA003340
AB100689,MA003351
AB100690,AB100687
AB100690,MA003354
AB100690,AB100731
AB100691,MA003353
Thanks,
Craig
--------------------------------------------------------
This e-mail, including any attachments, may be confidential, privileged or
otherwise legally protected. It is intended only for the addressee. If you
received this e-mail in error or from someone who was not authorized to send it
to you, do not disseminate, copy or otherwise use this e-mail or its
attachments. Please notify the sender immediately by reply e-mail and delete
the e-mail from your system.
