Does anyone here know SQL well? Help needed.

[Piglet]

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:

Code: [Select]


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;
}

[Caracal]

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

[Caracal]

Piglet, did it help?

[Piglet]

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...

[Caracal]

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:

Code: [Select]

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

[Pyc]

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):

Code: [Select]

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:

Code: [Select]

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;