Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
1

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.

share|improve this question

4 Answers

up vote 5 down vote accepted

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);
share|improve this answer
@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 !!! – RolandoMySQLDBA Jun 10 '11 at 20:21
@Rolando Thanks. It also doesn't require reading the data twice. Will this work in MySQL? – Leigh Riffel Jun 10 '11 at 20:25
@Leigh Wow, it works !!! I guess I over-engineered my answer !!! – RolandoMySQLDBA Jun 10 '11 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 ? – tostinni Jun 10 '11 at 21:27
@tostinni Sorry, PARTITION BY is not supported by MySQL dialect of SQL. – RolandoMySQLDBA Jun 10 '11 at 21:35
show 3 more comments
up vote 1 down vote

MySQL Solution

I think I got it !!!!

Query for the First Answer:

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);

Query for the Second Answer:

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 !!!

share|improve this answer
Hum... but what about the fact (IMHO) that excluding a column in a GROUP BY is bad mojo from MySQL ? – tostinni Jun 10 '11 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. – RolandoMySQLDBA Jun 10 '11 at 21:48
So any new take on this one ? – tostinni Jun 10 '11 at 21:50
Not sure I understood your comment ? – tostinni Jun 10 '11 at 21:57
up vote 0 down vote

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...

share|improve this answer
IFNULL doesn't exist on Oracle. This will work if you change it to COALESCE. Does that function exist in MySQL? – Leigh Riffel Jun 11 '11 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. – Leigh Riffel Jun 11 '11 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. – tostinni Jun 11 '11 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? – Leigh Riffel Jun 11 '11 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. – tostinni Jun 11 '11 at 21:47
up vote -1 down vote

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)
share|improve this answer
3  
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. – tostinni Jun 10 '11 at 18:44
You asked for Oracle OR MySQL, not Oracle AND MySQL. Flawed behavior or feature? :-) – randy melder Jun 13 '11 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 ;). – tostinni Jun 13 '11 at 20:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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