I can write a program to do this, but was wondering whether there is a way to do it in a single SQL statement.
A table has fields including: timestamp, p, s, and a.
I'm looking for a command to select (and when I'm happy it working, delete) all but the latest 30 timestamp'd entries for each (p, s, and a) combination.
I could write a program to to do it but I wonder if a single complex sql statement could do it...
This is the pseudo-code for what the program woud look like:
select distinct s,p,a from tablename;
for each ($s,$p,$a) row returned{
select min(timestamp) as $mt from (select timestamp from tablename where p = $p and s=$s and a = $a order by timestamp desc limit 30 ) as s1;
select/delete from tablename where timestamp < $mt and p = $p and s=$s and a = $a;
}
select * from
(
select id, s, p, a, timestamp, row_number() over (partition by s,p,a order by timestamp desc) as numeration
from table
) where numeration > 30
Piglet, did it help?
Hmmm. No joy.
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(partition by....
I'm using "mysql Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1"
Maybe "partition by" is not available...
I dont have an experience in Mysql, but this construction should work in Mysql. Maybe there are some other problems with namings.
Tested in Oracle, works perfectly:
select * from (
select EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, JOB_ID, HIRE_DATE,
row_number() over (partition by DEPARTMENT_ID, JOB_ID order by HIRE_DATE desc) as numeration
from EMPLOYEES
order by DEPARTMENT_ID, JOB_ID
) where numeration > 1
Hey guys, registered specially to answer this.
MySQL does not have row_number() function, but we can emulate it.
This SQL we can use to select (tested with MySQL 5.7):
SELECT
s, p, a, ts, rown
FROM
(SELECT
s, p, a, ts,
@rown := IF(@prevA = a AND @prevP = p AND @prevS = s, @rown + 1, 1) AS rown,
@prevA := a, @prevP := p, @prevS := s
FROM tablename
JOIN (SELECT @prevA := NULL, @prevP := NULL, @prevS := NULL, @rown := 0) AS vars
ORDER BY s, p, a, ts DESC) temp
WHERE rown <= 30;
... and this one to delete:
DELETE t FROM
tablename t,
(SELECT
s, p, a, MIN(ts) AS mints
FROM
(SELECT
s, p, a, ts,
@rown := IF(@prevA = a AND @prevP = p AND @prevS = s, @rown + 1, 1) AS rown,
@prevA := a, @prevP := p, @prevS := s
FROM tablename
JOIN (SELECT @prevA := NULL, @prevP := NULL, @prevS := NULL, @rown := 0) AS vars
ORDER BY s, p, a, ts DESC) AS temp
WHERE rown <= 30
GROUP BY s, p, a) lim
WHERE lim.s = t.s
AND lim.p = t.p
AND lim.a = t.a
AND lim.mints > t.ts;