I tried reading this and it took much brain power to do for free. I suggest breaking this down into the simplest possible example you can. Ideally have an create table statement, and insert with a small amount of data (as simple as possible), a simple psuedo code query and example output of what you want returned.
On Mon, Sep 29, 2008 at 2:00 PM, Weston, Craig (OFT) <[EMAIL PROTECTED]> wrote: > 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. > -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
