0

I have the following table:

+----+-------+---------+---------------+
| id | owner | variant | EAN           |
+----+-------+---------+---------------+
| 1  | 101   | black   | 1111111111111 |
+----+-------+---------+---------------+
| 2  | 102   | blue    | 1111111111112 |
+----+-------+---------+---------------+
| 3  | 103   | white   | 1111111111113 |
+----+-------+---------+---------------+
| 4  | 103   | white   | 1111111111114 |
+----+-------+---------+---------------+
| 5  | 103   | black   | 1111111111115 |
+----+-------+---------+---------------+
| 6  | 104   | white   | 1111111111116 |
+----+-------+---------+---------------+
| 7  | 104   | black   | 1111111111117 |
+----+-------+---------+---------------+

I want to select all rows except the one's that have the same owner and variant

My output has to be like this:

+----+-------+---------+---------------+
| id | owner | variant | EAN           |
+----+-------+---------+---------------+
| 1  | 101   | black   | 1111111111111 |
+----+-------+---------+---------------+
| 2  | 102   | blue    | 1111111111112 |
+----+-------+---------+---------------+
| 4  | 103   | white   | 1111111111114 |
+----+-------+---------+---------------+
| 5  | 103   | black   | 1111111111115 |
+----+-------+---------+---------------+
| 6  | 104   | white   | 1111111111116 |
+----+-------+---------+---------------+
| 7  | 104   | black   | 1111111111117 |
+----+-------+---------+---------------+

Can anyone explain me how this can be done? I have tried group by but that only works on 1 column

2
  • 2
    You've got owner=103 returning one variant, how do you determine which one to return?
    – Taryn
    Aug 15, 2014 at 13:24
  • @bluefeet Not noted, i want the newest EAN so SORT BY EAN ASC
    – Juun
    Sep 2, 2014 at 8:37

3 Answers 3

2

The particular EAN to render would be determined by the database.

Assuming your tablename is august15, then

using the sample data below +----+-------+---------+---------------+ | id | owner | variant | EAN | +----+-------+---------+---------------+ | 1 | 101 | black | 1111111111111 | +----+-------+---------+---------------+ | 2 | 102 | blue | 1111111111112 | +----+-------+---------+---------------+ | 3 | 103 | white | 1111111111113 | +----+-------+---------+---------------+ | 4 | 103 | white | 1111111111114 | +----+-------+---------+---------------+ | 5 | 103 | black | 1111111111115 | +----+-------+---------+---------------+ | 6 | 104 | white | 1111111111116 | +----+-------+---------+---------------+ | 7 | 104 | black | 1111111111117 | +----+-------+---------+---------------+

This query SELECT * FROM August15 GROUP BY owner, variant; would result

+----+-------+---------+---------------+ | id | owner | variant | EAN | +----+-------+---------+---------------+ | 1 | 101 | black | 1111111111111 | +----+-------+---------+---------------+ | 2 | 102 | blue | 1111111111112 | +----+-------+---------+---------------+ | 5 | 103 | black | 1111111111115 | +----+-------+---------+---------------+ | 3 | 103 | white | 1111111111113 | +----+-------+---------+---------------+ | 7 | 104 | black | 1111111111117 | +----+-------+---------+---------------+ | 6 | 104 | white | 1111111111116 | +----+-------+---------+---------------+

to get exactly what you need change the query to SELECT * FROM August15 GROUP BY owner, variant ORDER BY id ASC;

Sample Data SET FOREIGN_KEY_CHECKS=0;

-- Table structure for august15

DROP TABLE IF EXISTS august15; CREATE TABLE august15 ( id int(11) NOT NULL AUTO_INCREMENT, owner int(11) NOT NULL, variant varchar(50) NOT NULL, ean varchar(11) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

-- Records of august15

INSERT INTO august15 VALUES ('1', '101', 'black', '11111111111'); INSERT INTO august15 VALUES ('2', '102', 'blue', '11111111112'); INSERT INTO august15 VALUES ('3', '103', 'white', '11111111113'); INSERT INTO august15 VALUES ('4', '103', 'white', '11111111114'); INSERT INTO august15 VALUES ('5', '103', 'black', '11111111115'); INSERT INTO august15 VALUES ('6', '104', 'white', '11111111116'); INSERT INTO august15 VALUES ('7', '104', 'black', '11111111117');

0
0

GROUP BY will operate on multiple columns, but obviously you don't want to group all columns in your result set. The trick is to use aggregate functions on the non-grouped columns:

select  
  max(id) as id,
  owner,
  variant,
  max(AEN) as AEN
from variants
group by owner, variant
order by max(id), owner

This query will return the distinct combinations of owner and variant with associated id and AEN. MIN and MAX are equivalent in this scenario because there is only one id and one AEN for a given combination of owner and variant - otherwise the table would contain duplicate rows.

Note: since MySQL 5.0, it's not necessary to use aggregate functions on non-grouped columns. I used the syntax above for clarity.

1
  • Note that you may well get an id from one record and an AEN from another. E.g., "{3, 103, white, ...114}" (you wouldn't get that exact value, but unless id and AEN sort identically there will be instances of conflict). Aug 15, 2014 at 21:23
0

This question intrigued me, so I examined the solutions proposed.

Toniton's (+1 BTW for the DDL and DML) supposes a monotonically increasing correspondance between id and ean - sure that can work - but as so succinctly put by bluefeet - what is/are the OP's criteria?

Dartonw's solution gives the correct EANs (see below for criterion), but returns the highest id for that EAN, even though they're not necessarily in the same record - a possibility pointed out by Jon of all Trades.

I added some sample data which muddied the waters of Toniton's DML (see SQL below) by mixing it up a little and came up with the following solution.

I assumed that it was the EAN that was important - the ID being some randomly assigned PRIMARY KEY (AUTO_INCREMENT). My solution therefore is a combination of both Toniton's and dartonw's. Implicit assumption, EAN is unique.

It selects the highest EAN per owner/variant combination, regardless of id and correctly returns the corresponding id for that EAN. Should the opposite be the goal of the OP, please change EAN for id in the subselect.

SELECT * 
FROM august15 
WHERE ean IN 
(
  SELECT MAX(ean) 
  FROM august15 
  GROUP BY owner, variant 
  ORDER BY ean
);

Finally, a word to the OP. First, welcome to the forum. Just as important as telling us what you want is to explain why you want it. Could you also please give us DDL (CREATE TABLE blah(col1 col1_type, col2...) and some DML (INSERT INTO blah VALUES(col1_value, col2_value...);). This makes it easier for those of us trying to create test cases for our answers. You can put this up on SQLFiddle for example - or here of course if there's not too much data - you can always provide a downloadable script.

Take a look here (and links within) on recommendations about asking questions. This is a great forum, so if you want to get the most out of it, help us to help you! :-)

==== DDL and DML for table structure and data =======

INSERT INTO august15 VALUES ('1', '101', 'black', '11111111111'); 
INSERT INTO august15 VALUES ('2', '102', 'blue',  '11111111112'); 
INSERT INTO august15 VALUES ('3', '103', 'white', '11111111113'); 
INSERT INTO august15 VALUES ('4', '103', 'white', '11111111114'); 
INSERT INTO august15 VALUES ('5', '103', 'black', '11111111115'); 
INSERT INTO august15 VALUES ('6', '104', 'white', '11111111116'); 
INSERT INTO august15 VALUES ('7', '104', 'black', '11111111117');
INSERT INTO august15 VALUES ('8', '104', 'black', '11111111219'); 
INSERT INTO august15 VALUES ('9', '104', 'black', '11111111110');

Result of my SQL

+------+-------+---------+-------------+
| id   | owner | variant | ean         |
+------+-------+---------+-------------+
|    1 |   101 | black   | 11111111111 |
|    2 |   102 | blue    | 11111111112 |
|    4 |   103 | white   | 11111111114 |
|    5 |   103 | black   | 11111111115 |
|    6 |   104 | white   | 11111111116 |
|    8 |   104 | black   | 11111111219 |
+------+-------+---------+-------------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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