Exclude all the rows with the same lID that are within an hour

Databases
1

Hi guys, I need suggestion for show from my mysql table only the first row grouped by unique tblID and to exclude all the rows with the same tblID that are within an hour compared to the previous row.

E.g.:

+----------+---------------------+
| tblID    | theDate             |
+----------+---------------------+
| 77002221 | 2013-06-12 11:17:29 |
| 56009055 | 2013-06-12 11:17:29 |
| 77002221 | 2013-06-12 11:47:29 |
| 77002221 | 2013-06-12 12:17:29 |
| 77002221 | 2013-06-12 12:47:29 |
+----------+---------------------+

In this example I need show only this output:

+----------+---------------------+
| tblID    | theDate             |
+----------+---------------------+
| 77002221 | 2013-06-12 11:17:29 |
| 56009055 | 2013-06-12 11:17:29 |
| 77002221 | 2013-06-12 12:47:29 |
+----------+---------------------+

Because this rows compared to the first row with tblID 77002221 fall within one hour:

+----------+---------------------+
| tblID    | theDate             |
+----------+---------------------+
| 77002221 | 2013-06-12 11:47:29 |
| 77002221 | 2013-06-12 12:17:29 |
+----------+---------------------+

Can you help me?
Thank you in advance.

2

hello, I tried this query union but the output is incorrect:

mysql> SELECT
	tblID,
	theDate
FROM
	`timediff`
WHERE
	tblID NOT IN (
		SELECT
			tblID
		FROM
			`timediff`
		WHERE
			(
				theDate >= ADDTIME(theDate, '1:01:01')
			)
	)
UNION ALL
	SELECT
		tblID,
		theDate
	FROM
		`timediff`;
+----------+---------------------+
| tblID    | theDate             |
+----------+---------------------+
| 77002221 | 2013-06-12 11:17:29 |
| 56009055 | 2013-06-12 11:17:29 |
| 77002221 | 2013-06-12 11:47:29 |
| 77002221 | 2013-06-12 12:17:29 |
| 77002221 | 2013-06-12 12:47:29 |
| 77002221 | 2013-06-12 11:17:29 |
| 56009055 | 2013-06-12 11:17:29 |
| 77002221 | 2013-06-12 11:47:29 |
| 77002221 | 2013-06-12 12:17:29 |
| 77002221 | 2013-06-12 12:47:29 |
+----------+---------------------+
10 rows in set

Instead of:

+----------+---------------------+
| tblID    | theDate             |
+----------+---------------------+
| 77002221 | 2013-06-12 11:17:29 |
| 56009055 | 2013-06-12 11:17:29 |
| 77002221 | 2013-06-12 12:47:29 |
+----------+---------------------+

DROP TABLE IF EXISTS `timediff`;
CREATE TABLE `timediff` (
  `tblID` int(10) DEFAULT NULL,
  `theDate` datetime DEFAULT NULL,
  `id` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of timediff
-- ----------------------------
INSERT INTO `timediff` VALUES ('77002221', '2013-06-12 11:17:29', '1');
INSERT INTO `timediff` VALUES ('56009055', '2013-06-12 11:17:29', '2');
INSERT INTO `timediff` VALUES ('77002221', '2013-06-12 11:47:29', '3');
INSERT INTO `timediff` VALUES ('77002221', '2013-06-12 12:17:29', '4');
INSERT INTO `timediff` VALUES ('77002221', '2013-06-12 12:47:29', '5');
3

Resolved: :smiley:

  1. Create a second table timediff1;
  2. Insert the min data value in table timediff1 for tblID;
  3. Join from table timediff and timediff1 and check the timediff.
mysql> DROP TABLE
IF EXISTS `timediff1`;

CREATE TABLE `timediff1` (
	`tblID` INT (10) DEFAULT NULL,
	`theDate` datetime DEFAULT NULL,
	`id` INT (10) NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`)
) ENGINE = MyISAM;

INSERT INTO `timediff1` (tblID, theDate)(
	SELECT
		tblID,
		MIN(thedate)
	FROM
		timediff
	GROUP BY
		tblID
);

SELECT
	a.*, b.*, TIMEDIFF(a.thedate, b.thedate) AS diff
FROM
	`timediff` a
JOIN `timediff1` b ON a.tblID = b.tblID
WHERE
	(
		TIMEDIFF(a.thedate, b.thedate) = '00:00:00'
		OR TIMEDIFF(a.thedate, b.thedate) >= '01:01:01'
	);
Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

+----------+---------------------+----+----------+---------------------+----+----------+
| tblID    | theDate             | id | tblID    | theDate             | id | diff     |
+----------+---------------------+----+----------+---------------------+----+----------+
| 77002221 | 2013-06-12 11:17:29 |  1 | 77002221 | 2013-06-12 11:17:29 |  2 | 00:00:00 |
| 56009055 | 2013-06-12 11:17:29 |  2 | 56009055 | 2013-06-12 11:17:29 |  1 | 00:00:00 |
| 77002221 | 2013-06-12 12:47:29 |  5 | 77002221 | 2013-06-12 11:17:29 |  2 | 01:30:00 |
+----------+---------------------+----+----------+---------------------+----+----------+
3 rows in set

Or

mysql> SELECT
	b.*
FROM
	(
		SELECT
			min(theDate) CTIME,
			addtime(min(theDate), '01:00:00') CTIME_1
		FROM
			timediff
	) a,
	timediff b
WHERE
	b.theDate = a.CTIME
OR b.theDate > a.CTIME_1;
+----------+---------------------+----+
| tblID    | theDate             | id |
+----------+---------------------+----+
| 77002221 | 2013-06-12 11:17:29 |  1 |
| 56009055 | 2013-06-12 11:17:29 |  2 |
| 77002221 | 2013-06-12 12:47:29 |  5 |
+----------+---------------------+----+
3 rows in set