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;
This page was last updated on May 01, 2006 04:28 PM.