CODEWITHSUNDEEP
mysqlselect
Alex McDaid
Alex McDaid

Reputation: 269

Group by multiple rows in any order

I am using Mysql and PHP

If I have a table

-------------------
| no1 | no2 | no3 | 
|-----|-----|-----|
|  A  |  B  |  C  |
|  B  |  C  |  A  |
|  C  |  B  |  A  |
-------------------

I would like to return the unique combinations of rows 

SELECT `no1`, `no2`, `no3`
FROM  `items` 
GROUP BY `no1', `no2`, `no3`

I would like this to return only one row, as the combination of fields is the same if you ignore the order.

How would I go about this?

Upvotes: 0

Views: 102

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

If you have only two columns, this is easy:

select distinct least(col1, col2), greatest(col1, col2)
from t;

With three, this is a bit harder:

select distinct least(no1, no2, no3) as smallest,
       (case when no1 not in (least(no1, no2, no3), greatest(no1, no2, no3)) then no1
             when no2 not in (least(no1, no2, no3), greatest(no1, no2, no3)) then no2
             else no3
        end) as middle,
      greatest(no1, no2, no3) as biggest
from items;

Note that distinct is a more succinct way of getting distinct groups.

EDIT:

If you want to do this for more columns, MySQL doesn't offer the nth() function (analogous to least() and greatest(). You can do the following. Unpivot the values (assuming there is an id on each row), then use group_concat() with the order by option:

select distinct nos
from (select id, group_concat(noi order by noi) as nos
      from (select id,
                   (case when n.n = 1 then no1
                         when n.n = 2 then no2
                         when n.n = 3 then no3
                    end) as noi
            from items i cross join
                 (select 1 as n union all select 2 union all select 3) n
           ) nn
      group by id
     ) nn;

This will give you back the values as a comma separated list.

Upvotes: 1

jaczes
jaczes

Reputation: 1394

try that:

SELECT group_concat(concat(`no1`, `no2`, `no3`) as STH
FROM  `items` 
GROUP BY `no1', `no2`, `no3`

Upvotes: 0

Fluffeh
Fluffeh

Reputation: 33512

Short of writing a function used by your mysql server, the only thing I can think of is to use a combination of both SQL and PHP like this:

SELECT distinct
    `no1`, `no2`, `no3`
FROM  `items`

This will give the disinct rows, then we can do this trick. Pop it into an array and sort each row:

$array=(0 => 'C', 1 => 'B', 2 => 'A');
sort($array);

Gather all your new bits into a single multidimensional array and use a array_unique on them to only get the distinct values you want.

You could also replicate the same functionality of ordering the columns alphabetically in MYSQL though that wold no doubt be quite tricky.

Upvotes: 0

Related Questions