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

Answer 1

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 ║
╚════╩══════════════════════════════╩═══════════╩════════════╩══════════╩══════════╩═══════╩═══════╩═══════╝