Correlated query example

				
--**************************************
-- Name: Correlated query example
-- Description: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.
-- By: Umachandar
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=196&lngWId=5--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