Hello,
i have a table wds_data like this (postgresql)
idx (pk) | timestamp | place | value
integer | timestamp | integer | integer
---------------------------------------------------------
1 | 2016-01-04 06:03 | 1 | 4
2 | 2016-01-04 06:05 | 2 | 3
3 | 2016-01-04 06:13 | 3 | 1
4 | 2016-01-04 06:23 | 1 | 12
5 | 2016-01-04 06:24 | 2 | 14
6 | 2016-01-04 07:02 | 3 | 1
7 | 2016-01-04 12:04 | 1 | 12
8 | 2016-01-04 14:03 | 2 | 29
9 | 2016-01-04 14:05 | 3 | 3
i want to delete the oldest values for each place in a cyclic task.
the SQL-Query for this job is
DELETE
FROM wds_data
WHERE (idx) IN (SELECT idx
FROM (SELECT t.idx,
ROW_NUMBER() OVER (PARTITION BY place ORDER BY timestamp DESC) AS r
FROM wds_data t) x
WHERE (x.r > 1000));
is there a way to do this in aurelius with orm?
thanks Matthias
The subquery can be done, I mean you can retrieve data, sort by oldest values and limit the query to return X records (Take). But there is no way to delete multiple records at once, you would have to load the objects you want to delete and remove one by one from Delphi code.
Hello Wagner,
i realised the delete-job with a IDBStatement (see sourcecode below).
Is this a supported way?
procedure TWDS_MSH7.Delete_Records;
var
Statement : IDBStatement;
begin
try
if DoTrace then
Trace_LZ_DS_Delete.Start;
try
Statement := Connection.CreateStatement;
Statement.SetSQLCommand(Format( 'DELETE ' +
'FROM wds_data ' +
'WHERE (idx) IN (SELECT idx ' +
' FROM (SELECT t.idx, ' +
' ROW_NUMBER() OVER (PARTITION BY place ORDER BY timestamp DESC) AS r ' +
' FROM wds_data t) x ' +
' WHERE (x.r > %d)); ', [Keep_Records_per_Group]));
Statement.Execute;
finally
if DoTrace then
Trace_LZ_DS_Delete.Stop(FormatDateTime('yyyy"-"mm"-"dd" "hh":"nn":"ss', Now));
end;
except
on E : Exception do
begin
SetError(DeleteRecords, E.Message);
Exit;
end;
end;
end;
regards Matthias
Yes, you can use the low-level database access interfaces available.