Select minimum value with group by in mysql

Question

My Database scheme,

CREATE TABLE `result` (
`ID` bigint(21) NOT NULL AUTO_INCREMENT,
`datetime` datetime DEFAULT NULL,
`recordnum` int(11) DEFAULT NULL,
`recordtype` int(11) DEFAULT NULL,
`minvalue` int(11) DEFAULT NULL,
`maxvalue` int(11) DEFAULT NULL,
`data1` int(11) DEFAULT NULL,
`data2` int(11) DEFAULT NULL,
`area` varchar(32) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `res` (`datetime`,`recordnum`,`area`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

Sample of data

INSERT INTO `result` VALUES ('1', '2013-03-26 12:03:31', '2', '1', '5', '10', '100', '200','Zone1');

INSERT INTO `result` VALUES ('2', '2013-03-26 12:03:31', '2', '1', '2', '7', '20', '20','Zone2');

INSERT INTO `result` VALUES ('3', '2013-03-26 12:03:31', '5', '2', '1', '2', '8', '15','Zone2');

INSERT INTO `result` VALUES ('4', '2013-03-26 12:00:31', '2', '1', '2', '7', '100', '200','Zone1');

INSERT INTO `result` VALUES ('5', '2013-03-26 12:00:31', '2', '1', '3', '9', '100', '200','Zone2');

INSERT INTO `result` VALUES ('6', '2013-03-26 12:00:31', '5', '2', '7', '2', '4', '10', 'Zone2');

INSERT INTO `result` VALUES ('7', '2013-03-25 12:03:31', '2', '1', '5', '10', '100', '200','Zone1');

INSERT INTO `result` VALUES ('8', '2013-03-25 12:03:31', '2', '1', '2', '7', '20', '20','Zone2');

INSERT INTO `result` VALUES ('9', '2013-03-25 12:03:31', '5', '2', '1', '2', '8', '15','Zone2');

INSERT INTO `result` VALUES ('10', '2013-03-25 12:00:31', '2', '1', '2', '7', '100', '200','Zone1');

INSERT INTO `result` VALUES ('11', '2013-03-25 12:00:31', '2', '1', '3', '9', '100', '200','Zone2');

INSERT INTO `result` VALUES ('12', '2013-03-25 12:00:31', '5', '2', '7', '2', '4', '10','Zone2');


INSERT INTO `result` VALUES ('13', '2013-03-25 11:03:31', '2', '1', '3', '10', '100','200', 'Zone1');

INSERT INTO `result` VALUES ('14', '2013-03-25 11:03:31', '2', '1', '8', '7', '20', '20','Zone2');

INSERT INTO `result` VALUES ('15', '2013-03-25 11:03:31', '5', '2', '2', '2', '8', '15','Zone2');

INSERT INTO `result` VALUES ('16', '2013-03-25 11:00:31', '2', '1', '1', '7', '100', '200','Zone1');

INSERT INTO `result` VALUES ('17', '2013-03-25 11:00:31', '2', '1', '9', '9', '100', '200','Zone2');

INSERT INTO `result` VALUES ('18', '2013-03-25 11:00:31', '5', '2', '12', '2', '4', '10','Zone2');

What I want is query every value when minvalue is minimum for each recordnum and area and also date

  +----+---------------------+-----------+-------+----------+----------+-------+-------+------------+
    | ID | datetime            | recordnum | area  |minvalue  | maxvalue | data1 | data2 | recordtype |
    +----+---------------------+-----------+-------+----------+----------+-------+-------+------------+
    |  2 | 2013-03-26 12:03:31 |         2 | Zone2 |        2 |        7 |    20 |    20 |          1 |
    |  3 | 2013-03-26 12:03:31 |         5 | Zone2 |        1 |        2 |     8 |    15 |          2 |
    |  4 | 2013-03-26 12:00:31 |         2 | Zone1 |        2 |        7 |   100 |   200 |          1 |
    |  8 | 2013-03-25 12:03:31 |         2 | Zone2 |        2 |        7 |    20 |    20 |          1 |
    |  9 | 2013-03-25 12:03:31 |         5 | Zone2 |        1 |        2 |     8 |    15 |          2 |
    | 16 | 2013-03-25 11:00:31 |         2 | Zone1 |        1 |        7 |   100 |   200 |          1 |
    +----+---------------------+-----------+-------+----------+----------+-------+-------+------------+

This query below gave good result but still has duplicate record displayed, please help

SELECT  a.*
FROM    result a
        JOIN
        (
            SELECT  datetime as mindatetime,DATE(datetime) dateOnly,area, recordNum, MIN(minvalue) min_val
            FROM    result
            GROUP   BY dateOnly,area, recordNum
        ) b  ON a.area = b.area AND
                a.recordNUM = b.recordNum AND
                a.minvalue = b.min_val ;
+----+---------------------+-----------+------------+----------+----------+-------+-------+-------+
| ID | datetime            | recordnum | recordtype | minvalue | maxvalue | data1 | data2 | area  |
+----+---------------------+-----------+------------+----------+----------+-------+-------+-------+
|  2 | 2013-03-26 12:03:31 |         2 |          1 |        2 |        7 |    20 |    20 | Zone2 |
|  2 | 2013-03-26 12:03:31 |         2 |          1 |        2 |        7 |    20 |    20 | Zone2 |
|  3 | 2013-03-26 12:03:31 |         5 |          2 |        1 |        2 |     8 |    15 | Zone2 |
|  3 | 2013-03-26 12:03:31 |         5 |          2 |        1 |        2 |     8 |    15 | Zone2 |
|  4 | 2013-03-26 12:00:31 |         2 |          1 |        2 |        7 |   100 |   200 | Zone1 |
|  8 | 2013-03-25 12:03:31 |         2 |          1 |        2 |        7 |    20 |    20 | Zone2 |
|  8 | 2013-03-25 12:03:31 |         2 |          1 |        2 |        7 |    20 |    20 | Zone2 |
|  9 | 2013-03-25 12:03:31 |         5 |          2 |        1 |        2 |     8 |    15 | Zone2 |
|  9 | 2013-03-25 12:03:31 |         5 |          2 |        1 |        2 |     8 |    15 | Zone2 |
| 10 | 2013-03-25 12:00:31 |         2 |          1 |        2 |        7 |   100 |   200 | Zone1 |
| 16 | 2013-03-25 11:00:31 |         2 |          1 |        1 |        7 |   100 |   200 | Zone1 |
+----+---------------------+-----------+------------+----------+----------+-------+-------+-------+

possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) — Marc B, Mar 26 '13 at 05:14

score 10 · Answer 1 · answered Mar 26 '13 at 05:16

There are many variations on how to solve this problem. One is by using a separate subquery which gets the minimum minvalue for every area and recordNum. The result of the subquery is then joined back on the original table so you can get all the columns within the row.

SELECT  a.*
FROM    result a
        INNER JOIN
        (
            SELECT  area, recordNum, MIN(minvalue) min_val
            FROM    result
            GROUP   BY area, recordNum
        ) b  ON a.area = b.area AND
                a.recordNUM = b.recordNum AND
                a.minvalue = b.min_val

SQLFiddle Demo

OUTPUT

╔════╦══════════════════════════════╦═══════════╦════════════╦══════════╦══════════╦═══════╦═══════╦═══════╗
║ ID ║           DATETIME           ║ RECORDNUM ║ RECORDTYPE ║ MINVALUE ║ MAXVALUE ║ DATA1 ║ DATA2 ║ AREA  ║
╠════╬══════════════════════════════╬═══════════╬════════════╬══════════╬══════════╬═══════╬═══════╬═══════╣
║  2 ║ March, 26 2013 12:03:31+0000 ║         2 ║          1 ║        2 ║        7 ║    20 ║    20 ║ Zone2 ║
║  3 ║ March, 26 2013 12:03:31+0000 ║         5 ║          2 ║        1 ║        2 ║     8 ║    15 ║ Zone2 ║
║  4 ║ March, 26 2013 12:00:31+0000 ║         2 ║          1 ║        2 ║        7 ║   100 ║   200 ║ Zone1 ║
╚════╩══════════════════════════════╩═══════════╩════════════╩══════════╩══════════╩═══════╩═══════╩═══════╝

Thanks, query work perfectly, I have another question how to add group base on day , i added `group by DATE_FORMAT(DATETIME,'%Y-%m-%d')` so I want MINVALUE for one day but it's not working, thanks in adv. — user2114544, Mar 26 '13 at 06:58
what is lacking on the result here? do you want to format the date and why do you need to add it on group by clause?? — John Woo, Mar 26 '13 at 06:59
No, the query result is working, I want to know how to get the minimum value for daily basis. — user2114544, Mar 26 '13 at 07:01
I just update the sample of data on the top Question also the expected result, only 1 unique record base on combination date , recordnum and area when MINVALUE record is minimum. — user2114544, Mar 26 '13 at 07:42
Now i got the result but still need to remove duplicate record, any suggestion? — user2114544, Mar 26 '13 at 08:36

Select minimum value with group by in mysql

1 Answers1