I have a table, colA and colB are int(15).

They hold ID's so format is like below

Is it possible to select rows and only one of each include VV. So using below data only one row of 1 and 15 and one of 2 and 6 but not the 6 and 2

I don't believe it's possible via SQL but can it be done via a php function of some sort?

1 15
2 6
1 17
1 15
6 2

    So, the columns are foreign keys to the same other table? Could you make it such that colA is always less than colB? (The existing rows can be "rectified" once.) If so, a SELECT DISTINCT on the two columns should work.

    laserlight

    Yes they are foreign keys to the another table.

    "Could you make it such that colA is always less than colB"...

    No either could be higher, there may be a 6 2 with not 2 and 6.

        I'm not asking if colA is always less than colB: your example data already shows that it is not the case. I am asking you to make it so, i.e., to require that on insertion, colA is always less than colB, and to do rectification of the existing rows so that it becomes the case. I am not sure if you can enforce this in standard SQL, but you can certainly implement it in the PHP or other application code that handles the insertion.

        Likewise, I'd ask: since these are supposed to be foreign keys, do you really want to allow a duplicate pair of foreign keys, like the repeated colA=1 and colB=15 in your example? If not, this can be enforced with a unique constraint. If you do want to allow it, a SELECT DISTINCT on both columns would eliminate the duplicates anyway.

        Doing the duplicate elimination in PHP isn't hard (there's the array_unique function, and you just need to ensure that colA < colB in the PHP representation of the row), but if you can avoid it with little cost, why not?

        laserlight

        Right,
        No, because colA is a start point and colB is the end point, to reverse them would mean data is wrong elsewhere.

        Both colA and colB hold locations ID's from another table. Library, Office, Gym, etc.

        each row in the database i'm searching is a list of deliveries.

        I'm essentially trying to find all unique paths. Office-Gym, Library-Office etc.

            a month later

            So if there is a 2–6 pair and also a 6–2 pair, which one do you want? What if it's 4–3 and 3–4?

              This is ugly, but I'll just throw it out there anyway. 🙂

              select distinct
  case when colA < colB then colA else colB end as foo_1,
  case when colA < colB then colB else colA end as foo_2,
  colC,
  colD
from . . .
                Write a Reply...