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.