--**************************************
--     
-- Name: Correlated query example
-- Description:Here is one example of a 
--     correlated query. This example uses a ta
--     ble that contains messages for each user
--     . This can be used to delete all but the
--      2 latest messages.
-- By: Umachandar
--
--This code is copyrighted and has-- limited warranties.Please see http://
--     www.Planet-Source-Code.com/xq/ASP/txtCod
--     eId.196/lngWId.5/qx/vb/scripts/ShowCode.
--     htm--for details.--**************************************
--     

CREATE TABLE #msg_tbl ( id int IDENTITY , userid int , msgid int , msgdate datetime)
INSERT #msg_tbl VALUES ( 1 , 1 , '1/1/00' )
INSERT #msg_tbl VALUES ( 1 , 2 , '1/2/00' )
INSERT #msg_tbl VALUES ( 1 , 3 , '1/3/00' )
INSERT #msg_tbl VALUES ( 2 , 1 , '2/1/00' )
INSERT #msg_tbl VALUES ( 2 , 2 , '2/2/00' )
INSERT #msg_tbl VALUES ( 2 , 3 , '2/3/00' )
INSERT #msg_tbl VALUES ( 2 , 4 , '2/4/00' )
INSERT #msg_tbl VALUES ( 3 , 1 , '3/1/00' )
INSERT #msg_tbl VALUES ( 3 , 2 , '3/2/00' )
SELECT * FROM #msg_tbl
--- 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
    rollback
    -- 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
        rollback