cursor to insert into a table variable

I would appreciate some help in writing a cursor to populate a table variable based on a result set
All the cursor does is find all rows with same area ID and areaName and insert into the table variable the one with the latest modified date, if all modified dates are equal then insert the one with the latest created date.
This is the result set(id int, areaID int, areaName varchar(30), created datetime, modifieddatetime):
3 10 MA 2005-03-04 2005-03-04
6 10 MA 2005-05-04 2005-03-04
9 10 MA 2005-03-04 2005-03-04
1 52 PUERTO RICO 2005-03-04 2005-03-04
2 52 PUERTO RICO 2005-03-04 2005-03-04
4 52 PUERTO RICO 2005-03-04 2005-05-04
8 52 PUERTO RICO 2005-03-04 2005-03-04
5 5 NY 2005-04-04 2005-03-04
7 5 NY 2005-03-04 2005-03-04
table variable @.tempTable(id int, areaID int, areaName varchar(30), created datetime, modified datetime)
After running this cursor my temp table would contain:
6 10 MA 2005-05-04 2005-03-04
4 52 PUERTO RICO 2005-03-04 2005-05-04
7 5 NY 2005-03-04 2005-03-04
Any help would be appreciated

you don't need a cursor for this at all
this should do it, change YourTable to your table name

insert into @.tempTable(id ,areaID ,areaName , created , modified )
select t1.* from
YourTable t1 join(
select areaID ,areaName , max(created)as MaxCreated,max( modified) as MaxModified
from YourTable
group by areaID ,areaName) t2 on
t1.areaID = t2.areaID
and t1.areaName = t2.areaName
and t1.created = t2.MaxCreated
and t1.modified = t2.MaxModified

if it is possible that you can have created and modified that are the same for an area then you have to group on t1 and grab the min or max id

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||

The following query will do..

Select TwoCol.* From
TwoCol
Join
(
Select
IdWise.ColOne,
IdMaxModdate,
IdMaxCrdDate
From

(Select ColOne,ModifieddateTime,Count(*) ModDateCount from TwoCol
group By ColOne,ModifieddateTime) as ModDateWise

Join (Select ColOne,Count(*) IdCount,max(ModifieddateTime) IdMaxModdate, max(createddatetime) IdMaxCrdDate
From TwoCol Group By ColOne) as IdWise On ModDateWise.ColOne = IdWise.ColOne And IdMaxModdate = ModifieddateTime
) as T On T.ColOne = TwoCol.ColOne And ModifieddateTime = IdMaxModdate And createddatetime = IdMaxCrdDate