General Zone > Technology Area

Does anyone here know SQL well? Help needed.

<< < (2/2)

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

... and this one to delete:


--- Code: ---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;
--- End code ---

Navigation

[0] Message Index

[*] Previous page