# How to get a column A which has the smaller column B grouping by column C?

I'd like to get the ID of a dataset for each category (my grouping column) which has the smallest "order" column.
Here a set of data to explain my thoughts:

``````CREATE TABLE DATAS (
ID INT(2) ,
CATEGORY INT(2) ,
ORD INT(1)
);

INSERT INTO DATAS (ID, CATEGORY, ORD)
VALUES (1, 1, 3), (2, 1, 2), (3, 1, 1), (4, 2, 1), (5, 2, 2);
``````

Result expected:

``````ID   CATEGORY
-------------
3    1
4    2
``````

Bonus question, how to retrieve just one ID even if there's some duplicates for the "order" column (ORD) ?

``````INSERT INTO DATAS (ID, CATEGORY, ORD)
VALUES (1, 1, 3), (2, 1, 2), (3, 1, 1), (4, 2, 1), (5, 2, 1), (6, 3, NULL), (7, 3, NULL);
``````

Result expected (using a MIN for example or other suggestion):

``````ID   CATEGORY
-------------
3    1
4    2
6    3
``````

Oracle or MySQL queries are welcomed, thanks a lot.

Oracle solution to both problems:

``````SELECT ID, Category FROM (
SELECT FIRST_VALUE(ID) OVER
(PARTITION BY Category ORDER BY Ord) IDOfSmallestOrdForCategory
, Category, ID FROM DATAS
)
WHERE ID = IDOfSmallestOrdForCategory;
``````

Generic solution to both problems:

``````SELECT MIN(a.ID), a.Category FROM DATAS a
JOIN (SELECT CATEGORY, COALESCE(MIN(ORD),0) MINORD FROM DATAS GROUP BY CATEGORY) b
ON COALESCE(a.ORD,0) = b.MINORD AND a.Category = b.Category
GROUP BY a.Category;
``````

Oracle DDL/DML:

``````CREATE TABLE DATAS (
ID       Integer,
CATEGORY Integer,
ORD      Integer
);

INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (1, 1, 3);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (2, 1, 2);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (3, 1, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (4, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (5, 2, 2);

INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (1, 1, 3);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (2, 1, 2);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (3, 1, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (4, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (5, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (6, 3, NULL);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (7, 3, NULL);
``````
• @Leigh : Hey, that's sneaky. Using a partition to siphon data apart. I like sneaky. The SQL is a lot more elegant because of it. Bravo and +1 !!! Jun 10, 2011 at 20:21
• @Rolando Thanks. It also doesn't require reading the data twice. Will this work in MySQL? Jun 10, 2011 at 20:25
• @Leigh Wow, it works !!! I guess I over-engineered my answer !!! Jun 10, 2011 at 20:27
• Great answer, this is exactly what I was looking for. In fact I read about `PARTITION BY` yesterday but wasn't quite sure on how to apply this. Analytics functions by example. @Rolando which version of MySQL support this ? Jun 10, 2011 at 21:27
• @tostinni Sorry, PARTITION BY is not supported by MySQL dialect of SQL. Jun 10, 2011 at 21:35

MySQL Solution

I think I got it !!!!

``````SELECT A.ID,A.CATEGORY FROM
(SELECT ID,CATEGORY,ORD FROM DATAS GROUP BY ID,CATEGORY) A
INNER JOIN
(SELECT MIN(ORD) ORD FROM DATAS) B
USING (ORD);
``````

``````SELECT AA.ID,AA.CATEGORY FROM
(SELECT ID,CATEGORY,IFNULL(AAA.ORD,BBB.ORD) ORD FROM DATAS AAA,
(SELECT MIN(ORD) ORD FROM DATAS) BBB) AA
INNER JOIN
(SELECT MIN(ORD) ORD FROM DATAS) BB
USING (ORD)
GROUP BY AA.CATEGORY,AA.ORD;
``````

The second answer was a little difficult because I had to find a way to replace the NULL with the minumum ORD value. I hope this is what you were thinking to do with the NULL. If you look at subquery BBB, that held the minumum ORD value. I used that in a cartesian product with subquery AAA as the means to replace the NULL ORD value. The rest got easier after that.

Here is the example run for the First Query:

``````mysql> use tostinni
Database changed
mysql> DROP TABLE IF EXISTS DATAS;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE DATAS (
->     ID INT(2) ,
->     CATEGORY INT(2) ,
->     ORD INT(1)
-> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (1, 1, 3), (2, 1, 2), (3, 1, 1), (4, 2, 1), (5, 2, 2);
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM DATAS;
+------+----------+------+
| ID   | CATEGORY | ORD  |
+------+----------+------+
|    1 |        1 |    3 |
|    2 |        1 |    2 |
|    3 |        1 |    1 |
|    4 |        2 |    1 |
|    5 |        2 |    2 |
+------+----------+------+
5 rows in set (0.00 sec)

mysql> SELECT A.ID,A.CATEGORY FROM
-> (SELECT ID,CATEGORY,ORD FROM DATAS GROUP BY ID,CATEGORY) A
-> INNER JOIN
-> (SELECT MIN(ORD) ORD FROM DATAS) B
-> USING (ORD);
+------+----------+
| ID   | CATEGORY |
+------+----------+
|    3 |        1 |
|    4 |        2 |
+------+----------+
2 rows in set (0.00 sec)

mysql>
``````

Here is the example run for the Second Query:

``````mysql> DROP TABLE IF EXISTS DATAS;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE DATAS (
->     ID INT(2) ,
->     CATEGORY INT(2) ,
->     ORD INT(1)
-> );
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO DATAS (ID, CATEGORY, ORD)
-> VALUES (1, 1, 3), (2, 1, 2), (3, 1, 1), (4, 2, 1), (5, 2, 1), (6, 3, NULL), (7, 3, NULL);
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM DATAS;
+------+----------+------+
| ID   | CATEGORY | ORD  |
+------+----------+------+
|    1 |        1 |    3 |
|    2 |        1 |    2 |
|    3 |        1 |    1 |
|    4 |        2 |    1 |
|    5 |        2 |    1 |
|    6 |        3 | NULL |
|    7 |        3 | NULL |
+------+----------+------+
7 rows in set (0.00 sec)

mysql> SELECT AA.ID,AA.CATEGORY FROM
-> (SELECT ID,CATEGORY,IFNULL(AAA.ORD,BBB.ORD) ORD FROM DATAS AAA,(SELECT MIN(ORD) ORD
FROM DATAS) BBB) AA
-> INNER JOIN
-> (SELECT MIN(ORD) ORD FROM DATAS) BB
-> USING (ORD)
-> GROUP BY AA.CATEGORY,AA.ORD;
+------+----------+
| ID   | CATEGORY |
+------+----------+
|    3 |        1 |
|    4 |        2 |
|    6 |        3 |
+------+----------+
3 rows in set (0.00 sec)

mysql>
``````

Give it a Try !!!

• Hum... but what about the fact (IMHO) that excluding a column in a `GROUP BY` is bad mojo from MySQL ? Jun 10, 2011 at 21:42
• @tostinni : You are right, it is bad MOJO indeed. I never exclude columns so as to be sure the GROUP BY worked properly. I'll usually get all columns for GROUP BY in a subquery and then leave it out when using the subsquery. It helps me follow data transformations. Sometimes, trusting MySQL's ability to leave out columns in GROUP BY clauses may result in phantom changes due to the Query Optimizer. Jun 10, 2011 at 21:48
• So any new take on this one ? Jun 10, 2011 at 21:50
• Not sure I understood your comment ? Jun 10, 2011 at 21:57

Here is my less elegant solution than @Leigh's but Oracle and MySQL compatible ;)

``````SELECT MIN(ID) AS ID, D1.CATEGORY
FROM DATAS D1
WHERE NOT EXISTS (
SELECT NULL
FROM DATAS D2
WHERE D2.CATEGORY = D1.CATEGORY
AND D2.ID <> D1.ID
AND COALESCE(D2.ORD, 0 ) < COALESCE(D1.ORD, 0 )
)
GROUP BY D1.CATEGORY
``````

I don't like the fact I have to add a `GROUP BY` to filter duplicate...

• IFNULL doesn't exist on Oracle. This will work if you change it to COALESCE. Does that function exist in MySQL? Jun 11, 2011 at 12:07
• I'd be more concerned about having to query the data twice than the group by. In any case, since you can't use a PARTITION BY, you will probably have to query the data twice and do the group by. Jun 11, 2011 at 12:13
• @Leigh yep COALESCE exists in MySQL, I updated my answer. What do you mean by "query the data twice" ? I found these kind of queries very efficient in Oracle, of course you need an index on CATEGORY. Also I found that putting and `AND rownum = 1` in the `NOT EXISTS` clause speed things even more. Jun 11, 2011 at 18:48
• Without indexes it will have to retrieve the DATAS data twice to determine the results. This can be seen if you do an explain on the query. It is almost as bad as if the table in the inner select were different than the table in the outer select. It is still fast, but not as fast as a single scan (pass/retrieval) of the table is. Yes, I can see how the rownum would help, but the point remains. Does that help? Jun 11, 2011 at 19:09
• @Leigh I am very very surprised by the results of your Query, I build a test table with a million records and one category for each 5 records and your query kicks mine's a.. :D 14 seconds vs 1 minute. I'm stunned, for me these kind of queries were very expensive. My EXPLAIN show the use of indexes while your query make 2 TABLE ACCESS FULL and a MERGE. I'd love to discuss this more in depth with you but this is not a discussion board. I will consider this for other queries. Thanks a lot. Jun 11, 2011 at 21:47

This returns an answer to the first part:

``````mysql> SELECT ID, CATEGORY, MIN(ORD) FROM DATAS GROUP BY CATEGORY;
+------+----------+----------+
| ID   | CATEGORY | MIN(ORD) |
+------+----------+----------+
|    1 |        1 |        1 |
|    4 |        2 |        1 |
|    6 |        3 |     NULL |
+------+----------+----------+
3 rows in set (0.00 sec)
``````

The answer to second part is a bit more complex. You may only want one answer, but which answer do you want? Greatest ID? Least CATEGORY? Subjective. Otherwise this might do it:

``````mysql> SELECT * FROM (SELECT ID, CATEGORY, MIN(ORD) AS min FROM DATAS GROUP BY ID, CATEGORY) m GROUP BY min ORDER BY ID ASC;
+------+----------+------+
| ID   | CATEGORY | min  |
+------+----------+------+
|    1 |        1 |    3 |
|    2 |        1 |    2 |
|    3 |        1 |    1 |
|    6 |        3 | NULL |
+------+----------+------+
4 rows in set (0.01 sec)
``````
• May I disagree with you ;), your first answer isn't correct, it is based on a flawed behavior of MySQL which allows you to not group all columns. Doing this in ORACLE will correctly send you an error. And in MySQL I'm pretty sure the ID returned will depend on the dataset and could be incorrect in many cases. Jun 10, 2011 at 18:44
• You asked for Oracle OR MySQL, not Oracle AND MySQL. Flawed behavior or feature? :-) Jun 13, 2011 at 18:22
• IMHO this is a flawed behavior because I think it's not correct to let the SGBD define the value for the non grouped columns. But there may be some specific reason that MySQL have this "feature". For me, it's very disturbing when I forgot to group a column and MySQL returns me some strange results while Oracle will call me out ;). Jun 13, 2011 at 20:40