Best way to delete or not as the case may be...

Question

Best way to delete or not as the case may be...

Ben.Baker

Newbie

Points: 6
More actions
December 21, 2009 at 2:13 pm

#218878

Folks, I would greatly appreciate some advice if possible.
In essence am in the process of importing/cleaning which (fingers crossed) will then enable some trend analysis work to take place. This has lead to the creation of a static table within SQLS2005 with some 32M records.
The last piece of cleaning now requires me to remove all but the first record and last record series of "Selection_ID"s. Each "Selection_ID" will have one or more time stamped records within its series - Am now a little out of my depth on how to achieve this within SQLS2005 so any help would be most gratefully accepted.
Many thanks in anticpiation.
Ben

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

keith.gerritsen SSC Eights! Points: 973 More actions · Answer 1

Does this do what you mean?

Select Selection_ID, Min(time_stamp) as MinTimestamp, Max(time_stamp) as MaxTimestamp into #Boundary From YourStaticLog group by Selection_ID

Select S.* from YourStaticLog s inner join #boundary b on s.Selection_ID = b.selection_id and time_stamp=minTimestamp

union

Select S.* from YourStaticLog s inner join #boundary b on s.Selection_ID = b.selection_id and time_stamp=maxTimestamp

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

We need a little more to help. We could use the table structures, sample data demonstrating the problem, expected results based on the sample data. For assistance in this, please read the first article I have referenced below in my signature block regarding asking for assistance. It covers everything you need to post and how except for the expected results.

Ben.Baker Newbie Points: 6 More actions · Answer 3

Hi Folks,

Thnaks for the help so far.

With the holiday upon us will go through the suggestions and revert as soon there after.

Thanks again.

Ben

freeman.e.l SSC Veteran Points: 249 More actions · Answer 4

insert the records you want to save into a temporary table, drop and recreate the table in question, then insert the records from the temporary table into the newly created table. As an alternative to dropping and recreating the table in question you can truncate the table. You may need to set identity_insert (table) (on/off). This should speed up the process.