Post reply

Update all but last two rows of each category

  • July 29, 2013 at 8:01 am

    #296185

    I would like to update the complete flag of all the rows of a table except the most recent two rows of each category.

    The table has fields like:

    category - string,

    item - string,

    creation_date - string ('YYYYMMDDHHMI'),

    complete_flag - boolean,

    etc.

    Each category does not have the same amount of records with the same creation_date so I do not want to filter by creation_date.

    Is there a way to accomplish this?

    Thanks for your help,

    Fred

  • July 29, 2013 at 8:10 am

    #1636654

    Based on provided details I can suggets you to look into ROW_NUMBER() windowed function. It will allow you to write the query you want.

    If you need more detailed help, please provide table DDL and some test data (in form of insert statements). You can find how to do it from the link at the bottom of my signature.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • July 29, 2013 at 8:16 am

    #1636656

    -- Stare & Compare: check it's likely to work

    ;WITH Updater AS (

    SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY category ORDER BY creation_date DESC)

    FROM MyTable

    )

    SELECT *

    FROM Updater

    WHERE rn > 2

    -- Run the update

    ;WITH Updater AS (

    SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY category ORDER BY creation_date DESC)

    FROM MyTable

    )

    UPDATE Updater SET complete_flag = 1

    WHERE rn > 2

    Best guess based on the info provided. The article Eugene mentions is exceptionally good - have a read.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • July 30, 2013 at 7:20 am

    #1636971

    Thanks Chris. This solves the problem.

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

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