MYSQL delete all records except first and last for a given date

Question

This gives me all the records for a particular switch and port. I would like to delete all except the first and last one for each day.

/mysql -sN --user=root --password=notmypassword -e 
"SELECT * FROM collector.fibre 
WHERE fabric_switch_name = 'switch-1' 
  AND port = '0' 
  AND datatype = 'TxElements' 
  AND (time > '2016-05-31 00:00:00' AND time < '2016-05-31 23:59:59') "

score 2 · Accepted Answer · answered Mar 31 '17 at 16:37

I don't know how much time will it take to delete all this records but i think minutes of works just wait for it :

delete fb from collector.fibre fb 
INNER JOIN 
(
SELECT 
  distinct 
  f.id -- extract only the ids 
FROM 
  collector.fibre  f
  INNER JOIN (
    -- create a special table   day   min_time  max_time 
    select 
      date(time) as 'day',
      min(time) as 'min_time',
      max(time) as 'max_time'
    from 
      collector.fibre
     Where
        -- must be the same set of records 
        -- because we look for a dates of this switch
       fabric_switch_name = 'switch-1' 
       AND port = '0' 
       AND datatype = 'TxElements'
    group by date(time)
  )  fd on date(f.time) = fd.day  -- join the two table to have a special table see example down  
WHERE 
  fabric_switch_name = 'switch-1' 
  AND port = '0' 
  AND datatype = 'TxElements' 
  -- extract record witch the time is not
  -- the min or the max of that date 
  AND (f.time <> fd.min_time and f.time <> fd.max_time) 
) recs on fb.id = recs.id -- join the table and the result of query by the id now you will remove the records that exist in the result of the query

It looks like I have to manually select the database or I get "ERROR 1046 (3D000): No database selected" — nyitguy, Mar 31 '17 at 22:59
No worries I can either use database name in the command or add use database name; in the first line. — nyitguy, Mar 31 '17 at 23:22

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

by "first and last" I will assume you mean earliest and latest, so:

Select criteria in the given date and then deselect the minimum date and maximum date values.

It is assumed your time is stored in a DATETIME column.

    DELETE FROM collector.fibre 
         WHERE fabric_switch_name = 'switch-1' 
         AND port = '0' 
         AND datatype = 'TxElements' 
         AND (TIME(time) != 
             (
             SELECT MAX(TIME(time))
             FROM `collector`.`fibre` WHERE fabric_switch_name = 'switch-1' 
             AND port = '0' 
             AND datatype = 'TxElements'
             AND DATE(time) = '2016-05-30'
             )
             AND
             TIME(time) != (
             SELECT MIN(TIME(time))
             FROM `collector`.`fibre` WHERE fabric_switch_name = 'switch-1' 
             AND port = '0' 
             AND datatype = 'TxElements'
             AND DATE(time) = '2016-05-30'
             )
         ) 
         AND DATE(time) = '2016-05-30'

This is probably poorly written and does unfortunately use criteria repetition for the subqueries but the concept works as:

SubSelect by criteria and Not rows where the time column is MAX or MIN time values fitting said criteria

Reference : https://stackoverflow.com/a/7836444/3536236

So you can then input each day and remove non-earliest and non-latest values. If you want to cycle through the whole table in one fell swoop I would figure you want to create a Procedure but I don't know enough to help you much here. However your title says MYSQL delete all records except first and last for a given date which implies you will manually run it one date at a time as needed.

Example:

Example Set:

   fabric_switch_name |   datatype  |  port  |        time
 -------------------------------------------------------------------
       switch-1       |  TxElements |    2   |  2016-05-31 15:00:00
       switch-1       |  TxElements |    1   |  2016-05-30 22:10:00
       switch-2       |  TxElements |    0   |  2016-05-30 15:00:00
       switch-1       |  TxElements |    0   |  2016-05-29 10:20:10
       switch-1       |  TxElements |    0   |  2016-05-29 05:50:00
       switch-1       |  TxElements |    0   |  2016-05-29 19:50:00
       switch-1       |  TxElements |    5   |  2016-05-29 21:50:00
       switch-1       |  TxElements |    0   |  2016-05-29 20:11:40

Result Set:

   fabric_switch_name |   datatype  |  port  |        time
 -------------------------------------------------------------------
       switch-1       |  TxElements |    2   |  2016-05-31 15:00:00
       switch-1       |  TxElements |    1   |  2016-05-30 22:10:00
       switch-1       |  TxElements |    0   |  2016-05-29 05:50:00
       switch-2       |  TxElements |    0   |  2016-05-30 15:00:00
       switch-1       |  TxElements |    5   |  2016-05-29 21:50:00
       switch-1       |  TxElements |    0   |  2016-05-29 20:11:40

ERROR 1093 (HY000): You can't specify target table 'fibre' for update in FROM clause — nyitguy, Mar 31 '17 at 07:11
Sorry same error.. ERROR 1093 (HY000): You can't specify target table 'fibre' for update in FROM clause — nyitguy, Mar 31 '17 at 13:45
@nyitguy http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause — Martin, Mar 31 '17 at 14:51

DarbyM · Answer 3 · 2017-03-31T14:52:49.613

0

Give this a try:

DELETE 
FROM collector.fibre  
WHERE fabric_switch_name = 'switch-1'
AND port = '0' 
AND datatype = 'TxElements'
AND time BETWEEN '2016-05-31 00:00:00' AND '2016-05-31 23:59:59' 
AND time NOT IN (
    SELECT MIN(time), MAX(time)
    FROM collector.fibre  
    WHERE fabric_switch_name = 'switch-1'
    AND port = '0' 
    AND datatype = 'TxElements'
    AND time BETWEEN '2016-05-31 00:00:00' AND '2016-05-31 23:59:59' 
)

edited Mar 31 '17 at 14:52

answered Mar 30 '17 at 20:33

DarbyM

1,173
2
9
25

Whats it give you? – DarbyM Mar 30 '17 at 20:49
can you explain what this does? Code only answers are rarely *helpful* – Martin Mar 30 '17 at 20:52
Well according to his first Response. it doesn't do anything! lol I can clarify further for others once the issue is resolved. – DarbyM Mar 30 '17 at 20:56
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BETWEEN '2016-05-31 00:00:00' AND '2016-05-31 23:59:59' )' at line 13 – nyitguy Mar 31 '17 at 07:26
I was missing the column name time before the second BETWEEN. I still haven't gotten a chance to sit down and confirm my syntax with some sample data.. but this may be worth trying again.. If it fails I will put together a sample data set and try it. – DarbyM Mar 31 '17 at 14:54

Charif DZ · Answer 4 · 2017-03-30T22:44:31.283

0

this query will extract all the record that should be deleted :

SELECT 
  * 
FROM 
  collector.fibre  f
  INNER JOIN (
    -- create a special table   day   min_time  max_time 
    select 
      date(time) as 'day',
      min(time) as 'min_time',
      max(time) as 'max_time'
    from 
      collector.fibre
     Where fabric_switch_name = 'switch-1'
    group by date(time)
  )  fd on date(f.time) = fd.day  -- join the two table to have a special table see example down  
WHERE 
  fabric_switch_name = 'switch-1' 
  AND port = '0' 
  AND datatype = 'TxElements' 
  -- extract record witch the time is not
  -- the min or the max of that date 
  AND (f.time <> fd.min_time and f.time <> fd.max_time)

let suppose this is your date

    1           2016-05-31 23:00:00 
    2           2016-05-31 01:00:00
    3           2016-05-31 00:00:00
    4           2016-05-30 10:00:12
    5           2016-05-30 01:00:00
    6           2016-05-30 00:05:00

the result of join :

    1           2016-05-31 23:00:00         2016-05-31   2016-05-31 00:00:00  2016-05-31 23:00:00  -- this will not be selected in the result
    2           2016-05-31 01:00:00         2016-05-31   2016-05-31 00:00:00  2016-05-31 23:00:00  -- this will be selected because it's not min or max of that day
    3           2016-05-31 00:00:00         2016-05-31   2016-05-31 00:00:00  2016-05-31 23:00:00 
    4           2016-05-30 10:00:00         2016-05-30   2016-05-30 00:05:00  2016-05-30 10:00:12
    5           2016-05-30 01:00:00         2016-05-30   2016-05-30 00:05:00  2016-05-30 10:00:12
    6           2016-05-30 00:00:00         2016-05-30   2016-05-30 00:05:00  2016-05-30 10:00:12

edited Mar 30 '17 at 22:44

answered Mar 30 '17 at 21:42

Charif DZ

14,415
3
21
40

i tried to review my code for syntax error as much as i could hope it help you – Charif DZ Mar 30 '17 at 21:44
let me know if you try it i love this kind of challenging queries ^_^ – Charif DZ Mar 30 '17 at 21:51
ERROR 1054 (42S22): Unknown column 'df.min_time' in 'where clause' – nyitguy Mar 30 '17 at 22:02
Change the name of sub query to df i wrote fd sorry dor this – Charif DZ Mar 30 '17 at 22:05
Ok i edited the query should work fine it was syntax error of sub query name – Charif DZ Mar 30 '17 at 22:10
I also forget to specify the switch name on subquery i think now is what you want it tell me if you try it – Charif DZ Mar 30 '17 at 22:46
I m using my phone adda the same condition to the subquery – Charif DZ Mar 31 '17 at 06:57
It finds over 8K records but how do I delete in the same query? Sorry for the delay response but VPN was not working for me. – nyitguy Mar 31 '17 at 07:30
1

Do you have a primay key on the table ? But i think i deserve up vote for this query – Charif DZ Mar 31 '17 at 09:14
I do have an Id as the primary key. – nyitguy Mar 31 '17 at 13:17
Ok i will edit my answer but after 3 hours from now at least but tell meis the result correct when you test it – Charif DZ Mar 31 '17 at 14:24

MYSQL delete all records except first and last for a given date

4 Answers4

Example: