General Zone > Technology Area

Does anyone here know SQL well? Help needed.

(1/2) > >>

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

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 * 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
--- End code ---

Navigation

[0] Message Index

[#] Next page