Post reply

stored procedure to constrain records of a certain type to a specified number

  • December 4, 2006 at 7:52 am

    #92046

    if this question has been asked and answered before i apologize for posting again. not sure exactly how to word this.

    i have a need to run a stored procedure that operates on a table of data that has multiple records for multiple identifiers. there may be 50 identifiers with 200 records of data for each identifier. at one time every day new records will be added to the table for some or all of the 50 identifiers. what i need to do is keep the latest 200 records and prune out the remaining oldest records for each of the 50 identifiers. i need to order by date so that only the oldest records that are not within the 200 record count are removed. i cannot, however, just delete the records that are older than 200 days because there may be gaps in time where no data was added for certain identifiers. the bottom line is i need to just keep 200 records or less (in case there aren't 200 records of data yet) for each identifier.

    any ideas?

    thanks a boatload!

    --pkoanui

  • December 4, 2006 at 2:12 pm

    #675624

    need some more information... can you post the actual data structure of the table?

    do you want to keep the last 200 records per identifier, or just the last 200 records (assuming there is a datefield);

    the answer really depends on the data structure and requirements; this might kickstart an idea, but we really need the DDL:  Delete from sometable where ThePrimaryKey not In(select top 200 ThePrimaryKey  from sometable orderByTheDateField)

    if we are keeping the last 200 per identifier, then something like this might help us group to find what items have more than 200 occurrances to trim:

    select SomeIdentifier,count(SomeIdentifier) as NumOccurances from sometable group by SomeIdentifier having count(SomeIdentifier) > 200

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

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