declare @t table (rowid int identity(1,1), col1 int, col2 int)
insert into @t select 10 ,0 union all
select 9 ,0 union all
select 8 ,0 union all
select 7 ,0 union all
select 6 ,0 union all
select 3 ,0 union all
select 1 ,0 
select * from @t

update t
set t.col2 = t.col1 - (select top 1 t2.col1 from @t t2 where t2.rowid > t.rowid order by t2.rowid asc)
from @t t
select * from @t

delete from @t where rowid > (Select min(rowid) + 1 from @t where col2 > 1)

select * from @t

-- sample data:
declare @t table (x int)
insert into @t select 10 union all
select 9  union all
select 8  union all
select 7  union all
select 6  union all
select 3  union all
select 2 union all
select 1  

-- solution:

select t.* 
from @t t
inner join
	(select max(x) x from @t a
	where not exists (select * from @t b where a.x =b.x+1)) t2
on
	t.x >= t2.x

SELECT Sum(dbo.test1.quantity) as [QTY],
 datepart(month,test2.orderdate),
 datepart(YEAR,test2.orderdate),
 (dbo.GetMonthofGivenDate(test2.orderdate) + ' ' + right(datepart(year,test2.orderdate),2))as [MONTH]
FROM dbo.test1 INNER JOIN
	dbo.Test2 on dbo.test1.OrderID=dbo.Test2.OrderID Where (dbo.Test2.OrderDate Between convert(varchar, (getdate()-365), 1) AND convert(varchar, getdate(), 1))
 AND dbo.test1.ID=@Key
Group BY  dbo.test1.ID,
 datepart(year,test2.orderdate),
 datepart(month,test2.orderdate),
 dbo.GetMonthofGivenDate(test2.orderdate),
  right(datepart(year,test2.orderdate),2),
 (dbo.GetMonthofGivenDate(test2.orderdate)
+' '+right(datepart(year,test2.orderdate),2))
Order by dbo.test1.ID, datepart(year,test2.orderdate) desc, 
 datepart(month,test2.orderdate) desc

declare @t table (rowid int identity(1,1), col1 int, col2 int, col3 int, col4 varchar(5), col6 varchar(2), col7 int)

insert into @t 
select 153, 4, 2007, 'APR', '07',0 union all
select 241, 3 ,2007 ,'MAR' ,'07',0 union all
select 315, 2 ,2007 ,'FEB' ,'07',0 union all
select 251, 1 ,2007 ,'JAN' ,'07',0 union all
select 188, 12, 2006,'DEC' ,'06',0 union all
select 172, 11, 2006 ,'NOV' ,'06' ,0

select * from @t

update t
set t.col7 = abs(t.col2 - (select top 1 t2.col2 from @t t2 where t2.rowid > t.rowid order by t2.rowid asc))
from @t t
select * from @t

delete from @t where rowid > (Select min(rowid) from @t where col7 > 1)

select * from @t

quote:

---

Originally posted by reflex2dotnet

Thanks Dinakar
Pefect. IT worked, and it is giving me the expected result.
But this is extracitng from the result set, ie. we need the resultset for further filteration.
How can i do so that, the resultset will be the last filtered one.
ie, now i have to manually enter the result to the table variable.
How can i do so that, the result set of first qeury getting inserted into table variable, and i will be geting the filtered output, directly from SP

Thank you very much for your help

---

Declare @table2 table (columns...)
insert into @table2 (columns...)
select <columns...> from @table1

quote:

---

declare @t table (rowid int identity(1,1), col1 int, col2 int, col3 int, col4 varchar(5), col6 varchar(2), col7 int)

insert into @t
select 153, 4, 2007, 'APR', '07',0 union all
select 241, 3 ,2007 ,'MAR' ,'07',0 union all
select 315, 2 ,2007 ,'FEB' ,'07',0 union all
select 251, 1 ,2007 ,'JAN' ,'07',0 union all
select 188, 12, 2006,'DEC' ,'06',0 union all
select 172, 11, 2006 ,'NOV' ,'06' ,0

select * from @t

update t
set t.col7 = abs(t.col2 - (select top 1 t2.col2 from @t t2 where t2.rowid > t.rowid order by t2.rowid asc))
from @t t
select * from @t

delete from @t where rowid > (Select min(rowid) from @t where col7 > 1)

select * from @t

---