如何使用MySQL Group选择多个记录？

Question

I can't seem to use distinct or group by to group certain mySQL records together. This SQL query below just returns one line each - I want all the values in the CID though..

I want to select a random group by cid or the first set in the table.. I cant use AND CID=X .. is there a way to do it without ..

Table

id      pid     image                           sort_order      cid
-----
2474    50      data/low.jpg            2                       56
2473    50      data/hi.jpg             3                       59
2471    50      data/thumn.jpg      500                     59
2472    50      data/front.jpg      1000                    56
2470    50      data/back.jpg           1                       56

Query

SELECT *
FROM `ocm1__product_image`
WHERE `product_id` = '50'
GROUP BY `cid`
ORDER BY `ocm1__product_image`.`sort_order` ASC
LIMIT 0 , 30

This should return

id      pid     image                           sort_order      cid
2474    50      data/low.jpg            2                       56
2472    50      data/front.jpg      1000                    56
2470    50      data/back.jpg           1                       56

But it returns both colours.. can I not unique the group?

It returns this which is wrong, I want to list all cid unique values

id      pid     image                           sort_order      cid
2474    50      data/low.jpg            2                       56
2471    50      data/thumn.jpg      500                     59

Answer 1

This will return all the entries with the lowest cid for the requested pid.
It gives the same result as you say that you need, without giving a specific cid as a condition;

SELECT o1.*
FROM `ocm1__product_image` o1
LEFT JOIN `ocm1__product_image` o2
  ON o1.pid=o2.pid AND o1.cid > o2.cid
WHERE o1.`pid` = '50' AND o2.cid IS NULL
ORDER BY `o1`.`sort_order` ASC
LIMIT 0 , 30

An SQLfiddle to test with.