-2

Under MySQL I can not group the three separate data columns via GROUP BY clause.

This sample contains four brands, each brand has 2 models and 4 versions of engines. I need to extract 4 vehicles the brand, the model and version of engines is different. A truly unique result in the 3 columns.

----------+--------+----------+
Brand     | Model  | Version  |
----------+--------+----------+
Renault   | Clio   | Essence  |
Citroen   | C4     | GPL      |
Ford      | Fiesta | Gazole   |
Peugeot   | 206    | Electric |
----------+--------+----------+

This works 8/10.

SELECT brand, model, version 
    FROM cars WHERE version = (SELECT version FROM cars GROUP BY version ORDER BY RAND() LIMIT 1) 
GROUP BY brand

An example of the table

CREATE TABLE IF NOT EXISTS `cars` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `brand` varchar(255) NOT NULL,
  `model` varchar(255) NOT NULL,
  `version` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPRESSED;

INSERT INTO `cars` (`id`, `brand`, `model`, `version`) VALUES
    (1, 'Renault', 'Clio ', 'Essence'),
    (2, 'Renault', 'Clio ', 'Gazole'),
    (3, 'Renault', 'Clio ', 'GPL'),
    (4, 'Renault', 'Clio ', 'Electric'),
    (5, 'Renault', 'Modus', 'Essence'),
    (6, 'Renault', 'Modus', 'Gazole'),
    (7, 'Renault', 'Modus', 'GPL'),
    (8, 'Renault', 'Modus', 'Electric'),
    (9, 'Peugeot', '307', 'Essence'),
    (10, 'Peugeot', '307', 'Gazole'),
    (11, 'Peugeot', '307', 'GPL'),
    (12, 'Peugeot', '307', 'Electric'),
    (13, 'Peugeot', '206', 'Essence'),
    (14, 'Peugeot', '206', 'Gazole'),
    (15, 'Peugeot', '206', 'GPL'),
    (16, 'Peugeot', '206', 'Electric'),
    (17, 'Citroen', 'C4', 'Essence'),
    (18, 'Citroen', 'C4', 'Gazole'),
    (19, 'Citroen', 'C4', 'GPL'),
    (20, 'Citroen', 'C4', 'Electric'),
    (21, 'Citroen', 'C5', 'Essence'),
    (22, 'Citroen', 'C5', 'Gazole'),
    (23, 'Citroen', 'C5', 'GPL'),
    (24, 'Citroen', 'C5', 'Electric'),
    (25, 'Ford', 'Focus', 'Essence'),
    (26, 'Ford', 'Focus', 'Gazole'),
    (27, 'Ford', 'Focus', 'GPL'),
    (28, 'Ford', 'Focus', 'Electric'),
    (29, 'Ford', 'Fiesta', 'Essence'),
    (30, 'Ford', 'Fiesta', 'Gazole'),
    (31, 'Ford', 'Fiesta', 'GPL'),
    (32, 'Ford', 'Fiesta', 'Electric');

closed as unclear what you're asking by ypercubeᵀᴹ, Michael Green, Philᵀᴹ, mustaccio, RolandoMySQLDBA Apr 25 '16 at 1:55

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • But how do you decide which model and which version to pick for each brand? You have to define some conditions - at least "the one with lowest ID for each brand" or maybe actually the other side around - "unique brand per version"? Generally all rows are independent and the SELECT does evaluate each one without taking other into account. If you try to define some conditions which should apply between the rows, things get hard. – jkavalik Apr 23 '16 at 17:22
  • I travel one by one 4 unique brands, I select the model and not in this version of the brand previously traveled. Regarding the relationship between the columns thereof are obvious. – marc1598 Apr 23 '16 at 17:24
  • Then select "all" and to the "picking" in your application. Relationships between columns are obvious, but you try to implement relationships between rows which are not simply possible to describe in SQL without significant hacks. – jkavalik Apr 23 '16 at 17:30
  • 1
    Do you want just a random row from each brand? Perhaps a result that has one row from each brand and one row for each version? What are the specs of the table? What if the combinations (rows in the table) had 5 distinct brands but 4 distinct models? What should happen then? Please edit the question and clarify. – ypercubeᵀᴹ Apr 24 '16 at 0:38
1

Here is what I was offered, it is not optimized but it works.

If someone with a suggestion I'm interested.

CREATE TABLE IF NOT EXISTS temp AS
SELECT  
    tb1.id AS id1,
    tb1.brand   AS brand1,
    tb1.model   AS model1,
    tb1.version AS version1,
    tb2.id      AS id2,
    tb2.brand   AS brand2,
    tb2.model   AS model2,
    tb2.version AS version2,
    tb3.id      AS id3,
    tb3.brand   AS brand3,
    tb3.model   AS model3,
    tb3.version AS version3,
    tb4.id      AS id4,
    tb4.brand   AS brand4,
    tb4.model   AS model4,
    tb4.version AS version4
FROM `cars` AS tb1
    INNER JOIN `cars` AS tb2
        ON  tb2.brand   != tb1.brand
        AND tb2.model   != tb1.model
        AND tb2.version != tb1.version
        AND tb2.id      >  tb1.id
    INNER JOIN `cars` AS tb3
        ON  tb3.brand   != tb2.brand
        AND tb3.model   != tb2.model
        AND tb3.version != tb2.version
        AND tb3.id      >  tb2.id
        AND tb3.brand   != tb1.brand
        AND tb3.model   != tb1.model
        AND tb3.version != tb1.version
        AND tb3.id      >  tb1.id
    INNER JOIN `cars` AS tb4
        ON  tb4.brand   != tb3.brand
        AND tb4.model   != tb3.model
        AND tb4.version != tb3.version
        AND tb4.id      >  tb3.id
        AND tb4.brand   != tb2.brand
        AND tb4.model   != tb2.model
        AND tb4.version != tb2.version
        AND tb4.id      >  tb2.id
        AND tb4.brand   != tb1.brand
        AND tb4.model   != tb1.model
        AND tb4.version != tb1.version
        AND tb4.id      >  tb1.id
LIMIT 1

--------------
select * from sample
--------------

+-----+---------+--------+----------+-----+---------+--------+----------+-----+---------+--------+----------+-----+--------+--------+----------+
| id1 | brand1  | model1 | version1 | id2 | brand2  | model2 | version2 | id3 | brand3  | model3 | version3 | id4 | brand4 | model4 | version4 |
+-----+---------+--------+----------+-----+---------+--------+----------+-----+---------+--------+----------+-----+--------+--------+----------+
|   1 | Renault | Clio   | Essence  |  10 | Peugeot | 307    | Gazole   |  19 | Citroen | C4     | GPL      |  28 | Ford   | Focus  | Electric |
+-----+---------+--------+----------+-----+---------+--------+----------+-----+---------+--------+----------+-----+--------+--------+----------+

and get the result formatter

SELECT  id1 AS id,
        brand1   AS brand,
        model1   AS model,
        version1 AS version
FROM temp

UNION

SELECT  id2      AS id,
        brand2   AS brand,
        model2   AS model,
        version2 AS version
FROM temp

UNION

SELECT  id3      AS id,
        brand3   AS brand,
        model3   AS model,
        version3 AS version
FROM temp

UNION

SELECT  id4      AS id,
        brand4   AS brand,
        model4   AS model,
        version4 AS version
FROM temp

+----+---------+-------+----------+
| id | brand   | model | version  |
+----+---------+-------+----------+
|  1 | Renault | Clio  | Essence  |
| 10 | Peugeot | 307   | Gazole   |
| 19 | Citroen | C4    | GPL      |
| 28 | Ford    | Focus | Electric |
+----+---------+-------+----------+

Not the answer you're looking for? Browse other questions tagged or ask your own question.