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.