Datensätze löschen, dass die 100 neuesten übrig bleiben

[Hobbycoder]

Hi,

ich habe eine Tabelle bestehend aus einer ID, einem Timestamp und einigen Statistikzählern. In dieser sollen aber immer nur eine maximale Anzahl an Daten bestehen bleiben (z.B. 100).

Jetzt könnte ich zwar in Delphi die Anzahl der Datensätze abfrage und sooft den ersten löschen, bis die gewünschte Anzahl in der Tabelle vorhanden ist.

Gibt es da vielleicht über SQL eine elegantere Lösung?

[Sherlock]

Ein OnInsert Trigger vielleicht?

Sherlock

[Hobbycoder]

Das wäre eine Möglichkeit.

Wenn es irgendwie geht, würde ich das aber gerne über eine simples SQL-Statement lösen.

[hoika]

Hallo,
ein Trigger ist simples SQL, was halt nur automatisch angeworfen wird.

In etwa so:
Delete From Table1 T1 Where T1.Id Not In (Select First 100 T2.Id From Table1 T2 Order by T2.TimeStamp Desc)

Wobei das SubSelect mir nicht so gefällt.

[mkinzler]

SQL-Code:

delete 
  from <Tabelle>  
where 
  id in ( 
    select id from table <Tabelle> t1
    minus
    select id from table <Tabelle> t2 limit 100 desc
 );

[Hobbycoder]

Zitat:

Zitat von hoika (Beitrag 1415182)

Hallo,
ein Trigger ist simples SQL, was halt nur automatisch angeworfen wird.

Okay, vielleicht hatte ich mich missverständlich ausgedrückt. Der Trigger soll nicht in der DB laufen.

Zitat:

Zitat von hoika (Beitrag 1415182)

In etwa so:
Delete From Table1 T1 Where T1.Id Not In (Select First 100 T2.Id From Table1 T2 Order by T2.TimeStamp Desc)

Das könnte schon das sein, was ich gesucht habe.

Zitat:

Zitat von hoika (Beitrag 1415182)

Wobei das SubSelect mir nicht so gefällt.

Warum? (außer das es nicht ganz MySQL-Syntax ist, passt's doch)

[Hobbycoder]

Zitat:

Zitat von mkinzler (Beitrag 1415183)

SQL-Code:

delete 
  from <Tabelle>  
where 
  id in ( 
    select id from table <Tabelle> t1
    minus
    select id from table <Tabelle> t2 limit 100 desc
 );

Das Minus sich auch auf ganze Datensätze anwenden lässt, wusste ich noch gar nicht ;-) (Hab das bisher immer nur zum rechnen verwendet) Danke. Das werde ich mal ausprobieren.

[Schokohase]

Eine Möglichkeit wäre

SQL-Code:

CREATE TABLE Table1
    (`id` int not null auto_increment, `date` date, `data` varchar(6), primary key(id))
;
    
INSERT INTO Table1
    (`date`, `data`)
VALUES
    ('2018-01-01', '"test"'),
    ('2018-01-02', '"test"'),
    ('2018-01-03', '"test"'),
    ('2018-01-04', '"test"'),
    ('2018-01-05', '"test"')
;

create temporary table latest 
SELECT id from Table1 order by date desc limit 2;

delete from Table1
where id not in ( select id from latest );

drop table latest;

sqlfiddle

[Hobbycoder]

Hm...das ist jetzt aber blöd

Code:

Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Ich muss mich etwas korrigieren. Ist kein MySQL 5.6 sondern MySQL 8.0.12

[p80286]

@mkinzler
:thumb:

falls jemand noch nachlesen will: https://www.techonthenet.com/sql/minus.php

Gruß
K-H

[Uwe Raabe]

Wenn das ID-Feld tatsächlich ein Zähler ist und keine Lücken in der Tabelle vorkommen können, dann kann man den niedrigsten zu behaltenden ID (MinID) auch über MAX(ID) - 99 errechnen und auf diesem Wert das WHERE für den DELETE Befehl aufbauen.

Falls die obige Bedingung nicht zutrifft, würde ich den MinID über den bereits gezeigten SELECT LIMIT Befehl ermitteln. Ein DELETE ... WHERE ID < MinID ist vermutlich immer noch performanter als ein WHERE ID in (...)

[Hobbycoder]

Zitat:

Zitat von Uwe Raabe (Beitrag 1415189)

Wenn das ID-Feld tatsächlich ein Zähler ist und keine Lücken in der Tabelle vorkommen können, dann kann man den niedrigsten zu behaltenden ID (MinID) auch über MAX(ID) - 99 errechnen und auf diesem Wert das WHERE für den DELETE Befehl aufbauen.

Falls die obige Bedingung nicht zutrifft, würde ich den MinID über den bereits gezeigten SELECT LIMIT Befehl ermitteln. Ein DELETE ... WHERE ID < MinID ist vermutlich immer noch performanter als ein WHERE ID in (...)

Danke, die Lösung funktioniert ;-)

[Jumpy]

Mal ein Versuch ohne Top/Limit 100, weiß nicht ob das so ganz passt:

SQL-Code:

Delete From Table
Where ID in
(
Select T.ID From Table T
Group By T.ID
Having (Select count(Distinct ID) From Table Where ID>T.ID)>=100
)

[himitsu]

Warum umständlich mit Minus und Co. rumjoinen?

MySQL kennt zum LIMIT auch ein OFFSET.



Gut, beim DELETE nicht, aber beim SELECT, also

SQL-Code:

DELETE FROM table WHERE id IN (SELECT id FROM table ORDER BY id OFFSET 100)

.

Zitat:

SQL-Code:

Distinct ID

Wenn ID nicht per se schon einzigartig ist, dann läuft da gewaltig was schief. :stupid:

[DeddyH]

Dann musst Du aber auch absteigend sortieren.

[Hobbycoder]

Zitat:

Zitat von himitsu (Beitrag 1415193)

Gut, beim DELETE nicht, aber beim SELECT, also

SQL-Code:

DELETE FROM table WHERE id IN (SELECT id FROM table ORDER BY id OFFSET 100)

.

Aber laut MySQL-Doku (ich hab's aber nicht ausprobiert) bedarf OFFSET den Parameter LIMIT (Kann mich aber auch irren) , und der wird in Subselect nicht supportet.

[DasWolf]

Ich mache das nach jedem Ermitteln einer neuen ID immer wie folgt:

Delphi-Quellcode:

function TGetMaxID.GetMaxID: integer;
begin
  with TFDQuery.Create(nil) do
  try
    Connection := fConnection;
    sql.add('SELECT IdentityCol');
    sql.add('FROM MaxTable');
    sql.add('WHERE UPPER(rowguid) = :rowguid');
    Params[0].Value := AnsiUpperCase(fRowGuid);
    Prepare;
    open;

    Result := Fields[0].asInteger;

    // Bis auf die letzten 10 Einträge alle Daten aus ID-Tabelle löschen
    sql.clear;
    sql.add('DELETE FROM MaxTable WHERE ID_Max+10 <= :ID_Max');
    Params[0].DataType := ftInteger;
    Params[0].Value := Result;
    Prepare;
    ExecSQL;
  finally
    Free;
  end;
end;