CODEWITHSUNDEEP
mysqlsql
frops
frops

Reputation: 2365

How to delete rows from grouped data in MySQL

I have a table:

enter image description here

I want to leave a maximum of two entries per live_login, which records sorted by last_login_ts.

As a result, you should get:

enter image description here

! If is it possible, than using only SQL, without php and other tools.

P.S. Sorry for my english :)

ANSWER:

delete gr.* from my_table gr inner join (Select x.* from my_table x join my_table y on y.live_login = x.live_login and y.last_login_ts <= x.last_login_ts group by x.live_login, x.last_login_ts having count(*) > 2) gh on gh.live_login=gr.live_login and gh.dead_login=gr.dead_login;

Thank you, Strawberry!

Upvotes: 1

Views: 78

Answers (3)

Strawberry
Strawberry

Reputation: 33945

Select x.* 
  from my_table x 
  join my_table y 
    on y.live_login = x.live_login 
   and y.last_login_ts <= x.last_login_ts 
 group by x.live_login
     , x.last_login_ts 
having count(*) <= 2

Or something like that

Upvotes: 3

pala_
pala_

Reputation: 9010

This works, but is ugly, and probably suboptimal:

delete logins from logins
inner join (
  select l1.live_login, l1.last_login_ts second, q1.last_login_ts min 
    from logins l1
    left join
      (select live_login, min(last_login_ts) last_login_ts 
        from logins 
        group by live_login) q1
    on q1.last_login_ts < l1.last_login_ts 
      and l1.live_login = q1.live_login
    where not exists 
     (select 1 
       from logins 
       where live_login = q1.live_login 
         and logins.last_login_ts > q1.last_login_ts 
         and logins.last_login_ts < l1.last_login_ts) 
     and q1.last_login_ts is not null) q
on q.live_login = logins.live_login and logins.last_login_ts > q.second;

demo fiddle here: http://sqlfiddle.com/#!9/d3145/1

Upvotes: 1

jarlh
jarlh

Reputation: 44766

Edit: Apparently this ANSI/ISO SQL compliant answer doesn't work with MySQL. Can be used with most other dbms products anyway, so I don't delete the answer.

Delete a row if there are two (or more) newer rows with same live_login:

delete from tablename t1
where 2 <= (select count(*)
            from tablename t2
            where t2.live_login = t1.live_login
              and t2.last_login_ts > t1.last_login_ts)

Upvotes: 2

Related Questions