Skip to content

Instantly share code, notes, and snippets.

View yrsdi's full-sized avatar
👋
Hi

Yadi Rosadi yrsdi

👋
Hi
32 followers · 93 following
View GitHub Profile
@yrsdi
yrsdi / delRecord_01.sql
Last active March 26, 2018 22:50
Delete all data records except the last N data record Using JOIN & OFFSET
DELETE
FROM
`table` AS p
JOIN
( SELECT id
FROM `table`
ORDER BY id DESC
LIMIT 1 OFFSET N
) AS lim
ON p.id <= lim.id;
@yrsdi
yrsdi / DelRecord_02.sql
Last active March 26, 2018 22:51
Delete all data records except the last N data record Using Operator & OFFSET
DELETE FROM `table`
WHERE id <= (
SELECT id
FROM (
SELECT id
FROM `table`
ORDER BY id DESC
LIMIT 1 OFFSET N
) lim
)
@yrsdi
yrsdi / DelRecord_03.sql
Last active March 26, 2018 22:51
Delete all data records except the last N data record Using NOT IN
DELETE FROM `table`
WHERE id NOT IN (
SELECT id
FROM (
SELECT id
FROM `table`
ORDER BY id DESC
LIMIT N
) lim
);
@yrsdi
yrsdi / DelRecord_04.sql
Last active March 26, 2018 22:52
Delete all data records except the last N data record Using LEFT JOIN
DELETE c.*
FROM table c
LEFT JOIN
(
SELECT id
FROM table a
ORDER BY id DESC
LIMIT N
) b
ON a.id = b.id