Binaryworld - Delete all but the two latest messages for each user ... [ SQL Server -> T-SQL ]

Here is one example of a correlated query. This example uses a table that contains messages for each user. This can be used to delete all but the 2 latest messages.

Click here to copy the following block

CREATE TABLE #msg_tbl (
    id int identity , userid int , msgid int , msgdate datetime
);

INSERT INTO #msg_tbl VALUES ( 1 , 1 , '1/1/00' );
INSERT INTO #msg_tbl VALUES ( 1 , 2 , '1/2/00' );
INSERT INTO #msg_tbl VALUES ( 1 , 3 , '1/3/00' );
INSERT INTO #msg_tbl VALUES ( 2 , 1 , '2/1/00' );
INSERT INTO #msg_tbl VALUES ( 2 , 2 , '2/2/00' );
INSERT INTO #msg_tbl VALUES ( 2 , 3 , '2/3/00' );
INSERT INTO #msg_tbl VALUES ( 2 , 4 , '2/4/00' );
INSERT INTO #msg_tbl VALUES ( 3 , 1 , '3/1/00' );
INSERT INTO #msg_tbl VALUES ( 3 , 2 , '3/2/00' );
SELECT * FROM #msg_tbl;
/*
id     userid msgid msgdate
----------- ------ ----- ------------------------
     1   1   1 2000-01-01 00:00:00.000
     2   1   2 2000-01-02 00:00:00.000
     3   1   3 2000-01-03 00:00:00.000
     4   2   1 2000-02-01 00:00:00.000
     5   2   2 2000-02-02 00:00:00.000
     6   2   3 2000-02-03 00:00:00.000
     7   2   4 2000-02-04 00:00:00.000
     8   3   1 2000-03-01 00:00:00.000
     9   3   2 2000-03-02 00:00:00.000
*/
GO
-- Objective:
/*
    To keep only the two latest messages & delete the rest. Note
    that this solution assumes that the "msgid" column need not
    necessarily be sequential for each "userid". This requirement
    complicates the problem, otherwise it will be a simple delete
    with a WHERE clause "msgid > 2".
*/
-- All SQL versions
BEGIN TRAN
DELETE #msg_tbl
WHERE ( SELECT COUNT(*) FROM #msg_tbl t2
WHERE t2.userid = #msg_tbl.userid And
t2.msgdate >= #msg_tbl.msgdate ) > 2;
SELECT * FROM #msg_tbl;
-- Each UserID with the two latest messages only
/*
id     userid   msgid    msgdate
----------- ----------- ----------- ------------------------
     2      1      2 2000-01-02 00:00:00.000
     3      1      3 2000-01-03 00:00:00.000
     6      2      3 2000-02-03 00:00:00.000
     7      2      4 2000-02-04 00:00:00.000
     8      3      1 2000-03-01 00:00:00.000
     9      3      2 2000-03-02 00:00:00.000
*/
ROLLBACK;

--- SQL 70 version
BEGIN TRAN
DELETE #msg_tbl
WHERE #msg_tbl.id NOT IN ( SELECT TOP 2 t2.id
              FROM #msg_tbl t2
       WHERE t2.userid = #msg_tbl.userid
   ORDER BY t2.msgdate DESC);
SELECT * FROM #msg_tbl;
/*
id     userid   msgid    msgdate
----------- ----------- ----------- ------------------------
     2      1      2 2000-01-02 00:00:00.000
     3      1      3 2000-01-03 00:00:00.000
     6      2      3 2000-02-03 00:00:00.000
     7      2      4 2000-02-04 00:00:00.000
     8      3      1 2000-03-01 00:00:00.000
     9      3      2 2000-03-02 00:00:00.000
*/
ROLLBACK;