Loop through each ID with more than 3 records in the same table

Question

This is my table:

placeID | name | time

My goal is to remove the oldest records if the placeID has more than 3 record in the table.

It's pretty hard to understand, so example will make it clear:

1 | "Some Name" | *fresher timestamp*
1 | "Some Name" | *fresher timestamp*
1 | "Some Name" | *older timestamp* -- > Delete this result since there are more than 3 records, and this is the older than the rest
1 | "Some Name" | *older timestamp* -- > Delete this result since there are more than 3 records, and this is older than the rest
1 | "Some Name" | *fresher timestamp*
2 | "Some Name" | *fresher timestamp*
2 | "Some Name" | *fresher timestamp*

My trouble: How do I loop for each placeID that has more than 3 record in my table?

Removing the oldest results will not be a problem.

What I tried:

SELECT placeID,COUNT(placeID) FROM place_fbStatus WHERE count(placeID) > 3

Error: #1111 - Invalid use of group function

Another Try:

SELECT id, placeID
FROM place_fbStatus
HAVING COUNT( placeID ) >4
LIMIT 0 , 30

Problem: Show only one placeID

Bernd Buffen · Accepted Answer · 2015-09-12T20:06:09.293

2

Here my Answer:

First thing is to create a PRIMARY KEY on your Table. So it is easy to DELETE a ROW with this.

Create the Table:

CREATE TABLE `mytable` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `placeID` INT(11) DEFAULT NULL,
  `name` VARCHAR(32) DEFAULT NULL,
  `time` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `placeID` (`placeID`,`time`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Insert some stuff:

INSERT INTO `mytable` (`id`, `placeID`, `name`, `time`)
VALUES
    (1, 1, 'Some Name', '2015-01-01 00:00:00'),
    (2, 1, 'Some Name', '2015-01-02 00:00:00'),
    (3, 1, 'Some Name', '2015-01-10 00:00:00'),
    (4, 1, 'Some Name', '2015-01-09 00:00:00'),
    (5, 2, 'Some Name', '2015-02-01 00:00:00'),
    (6, 2, 'Some Name', '2015-01-30 00:00:00'),
    (7, 1, 'Some Name', '2015-01-08 00:00:00');

i add rid ( ROW ID per Place ) and the Rows are in the right order

SELECT @rid:=IF( @pid = m.placeID, @rid+1 , 1) rid, m.*, @pid:=m.placeID AS pid
      FROM mytable m, (SELECT @rid:=0, @pid:=-1) AS tmp 
      ORDER BY placeID,TIME ASC

And at last the Query to DELETE the ROWs:

DELETE mt FROM mytable mt
INNER JOIN (
  SELECT * FROM (
    SELECT @rid:=IF( @pid = m.placeID, @rid+1 , 1) rid, m.*, @pid:=m.placeID AS pid
      FROM mytable m, (SELECT @rid:=0, @pid:=-1) AS tmp 
      ORDER BY placeID,TIME ASC
  ) AS x
  WHERE x.rid >3
) AS dt ON mt.id = dt.id ;

edited Sep 12 '15 at 20:06

answered Sep 12 '15 at 11:43

Bernd Buffen

14,525
2
24
39

I'm not familiar with rid, but I tried it - and it doesn't delete any records. x.rid shouldn't be x.pid? – Eliran Sep 12 '15 at 18:34
can you post some data here or better do it on http://sqlfiddle.com/ . Post the CREATE table please – Bernd Buffen Sep 12 '15 at 18:37
You said that you have a TIMESTAMP but there is a VARCHAR Field. Plese show the Values in it and try my hole sample ( Create, Insert and Delete STatement) – Bernd Buffen Sep 12 '15 at 18:50
I'm sorry. It's UNIX timestamp. (Example: 1442082119). I didn't think it's matter. I store it as varchar. I think your query should work in both cases but it's not – Eliran Sep 12 '15 at 18:51
yes its ok, but a cant find any error. i have testet it again. Can you please also post your modified Delete Query – Bernd Buffen Sep 12 '15 at 18:56
Soory, i have insert his in your Table and the DELETE works. it deletes 2 ROWS - INSERT INTO `place_fbStatus` (`id`, `placeID`, `objID`, `theTime`) VALUES (1, 1, 'Some Name', UNIX_TIMESTAMP('2015-01-01 00:00:00')), (2, 1, 'Some Name', UNIX_TIMESTAMP('2015-01-02 00:00:00')), (3, 1, 'Some Name', UNIX_TIMESTAMP('2015-01-10 00:00:00')), (4, 1, 'Some Name', UNIX_TIMESTAMP('2015-01-09 00:00:00')), (5, 2, 'Some Name', UNIX_TIMESTAMP('2015-02-01 00:00:00')), (6, 2, 'Some Name', UNIX_TIMESTAMP('2015-01-30 00:00:00')), (7, 1, 'Some Name', UNIX_TIMESTAMP('2015-01-08 00:00:00')); – Bernd Buffen Sep 12 '15 at 19:08
No way.. I inserted the SAME rows using your insert query and it doesn't delete them... – Eliran Sep 12 '15 at 19:14
i dont now why. what says select version(); – Bernd Buffen Sep 12 '15 at 19:16
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/89480/discussion-between-bernd-buffen-and-eliran). – Bernd Buffen Sep 12 '15 at 19:28

score 0 · Answer 2 · answered Sep 12 '15 at 10:42

0

You almost nailed it. What you need is to combine GROUP BY and HAVING

SELECT placeID, count(placeID) AS cnt
  FROM place_fbStatus 
  GROUP BY placeID HAVING cnt>3

answered Sep 12 '15 at 10:42

olegsv

1,422
1
14
21

When I add `ORDER BY theTime ASC` or `ORDER BY theTime DESC` in both cases it shows the freshest record instaed of the oldest.. – Eliran Sep 12 '15 at 18:29
Your question was: "How do I loop for each placeID that has more than 3 record in my table?" If you need also to delete older records, you can go for @berndbuffen's solution, it's overcomplicated but it will work. – olegsv Sep 13 '15 at 12:49

score 0 · Answer 3 · edited May 23 '17 at 12:29

0

DELETE FROM Table WHERE ID NOT IN (SELECT TOP 3 ID FROM Table)

Refer to this thread

Although, it seems after trying it myself, it seems some versions of mysql dont yet support limit clauses in subqueries (This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'). To workaround this, try using an alternate subquery that doesnt involve limits.

edited May 23 '17 at 12:29

Community

1
1

answered Sep 12 '15 at 10:50

Onitz

46
3

Loop through each ID with more than 3 records in the same table

3 Answers3