sql : Delete all records except the 2 most recent for each combination(channel/cdn/mediaplaylist)

Question

What is the sql query to delete all records except the 2 most recent for each combination (channel/cdn/mediaplaylist).

For example, I have the following table:

channel       cdn        mediaplaylist      response    date

channel1      cdn1       mediaplaylist1     12          2015-02-09 10:40:00
channel2      cdn2       mediaplaylist2     19          2015-02-09 10:41:00   
channel3      cdn3       mediaplaylist3     16          2015-02-09 10:42:00
channel1      cdn1       mediaplaylist1     20          2015-02-09 10:43:00
channel1      cdn1       mediaplaylist1     57          2015-02-09 10:46:00
channel2      cdn2       mediaplaylist2     83          2015-02-09 10:47:00    
channel3      cdn3       mediaplaylist3     37          2015-02-09 10:48:00
channel1      cdn1       mediaplaylist1     43          2015-02-09 10:49:00
channel1      cdn1       mediaplaylist1     33          2015-02-09 10:50:00
channel2      cdn2       mediaplaylist2     79          2015-02-09 10:51:00    
channel3      cdn3       mediaplaylist3     19          2015-02-09 10:52:00
channel1      cdn1       mediaplaylist1     13          2015-02-09 10:53:00

I would like to get the following result :

channel       cdn        mediaplaylist      response    date

channel1      cdn1       mediaplaylist1     12          2015-02-09 10:40:00
channel1      cdn1       mediaplaylist1     20          2015-02-09 10:43:00
channel2      cdn2       mediaplaylist2     19          2015-02-09 10:41:00
channel2      cdn2       mediaplaylist2     83          2015-02-09 10:47:00
channel3      cdn3       mediaplaylist3     16          2015-02-09 10:42:00
channel3      cdn3       mediaplaylist3     37          2015-02-09 10:48:00

Actually i'm writing a php script and i would like to get the result from mysql — lipdjo, Feb 09 '15 at 14:02
possible duplicate of [Duplicates removing](http://stackoverflow.com/questions/7447675/duplicates-removing) — HoneyBadger, Feb 09 '15 at 14:02

score 3 · Answer 1 · answered Feb 09 '15 at 13:58

3

With SQL-Server it is easy, just use a CTE and a ranking function like ROW_NUMBER:

WITH CTE AS
(
    SELECT RN = ROW_NUMBER() OVER (Partition By channel, cdn, mediaplaylist 
                                Order By date DESC), 
           c.*  -- select all columns for debugging purposes
    FROM dbo.Channels c
)
DELETE FROM CTE WHERE RN > 2

I like common-table-expressions since they allow to see what i'm going to delete. Therefore you just need to replace DELETE with SELECT *.

answered Feb 09 '15 at 13:58

Tim Schmelter

450,073
74
686
939

You should not delete from the CTE, you should delete from the table while filtering using the CTE – Moslem Ben Dhaou Feb 09 '15 at 14:01
1

@MoslemBenDhaou: this will delete from the table `Channels`. I have presumed that table-name from the description. You can use this syntax if you don't use a `JOIN` in the CTE. – Tim Schmelter Feb 09 '15 at 14:02
Please , is it possible to introduce your sql code in a php script? i would like to get the result from mysql database. – lipdjo Feb 09 '15 at 14:04
Do you have any solution with mysql? – lipdjo Feb 09 '15 at 14:09
@lipdjo: MySql doesn't support ranking functions. I'm afraid i have no solution for MySql right now. – Tim Schmelter Feb 09 '15 at 14:18
If I add a Where clause to Channels, will it still have to partition the entire table or will it use the Where clause first then do the partition? – Rod Mar 30 '17 at 19:37
@rod: if you add the where to the inner query/cte you will get a different result since the filter is applied first. – Tim Schmelter Mar 31 '17 at 05:39

score 0 · Answer 2 · answered Feb 09 '15 at 14:31

The following query enumerates the rows as you have them:

select f.*,
       (@rn := if(@ccp = concat_ws(':', channel, cdn, mediaplaylist), @rn + 1,
                  if(@ccp := concat_ws(':', channel, cdn, mediaplaylist), 1, 1)
                 )
       ) as seqnum
from following f cross join
     (select @rn := 0, @ccp := '') vars
order by channel, cdn, mediaplaylist, date desc;

You want the values where seqnum is 1 or 2.

You can put this in a delete:

delete f from following f join (select f.*, (@rn := if(@ccp = concat_ws(':', channel, cdn, mediaplaylist), @rn + 1, if(@ccp := concat_ws(':', channel, cdn, mediaplaylist), 1, 1) ) ) as seqnum from following f cross join (select @rn := 0, @ccp := '') vars order by channel, cdn, mediaplaylist, date desc ) ccp on f.channel = ccp.channel and f.cdn = ccp.cdn and f.mediaplaylist = cdn.mediaplaylist where seqnum < 3;

sql : Delete all records except the 2 most recent for each combination(channel/cdn/mediaplaylist)

2 Answers2