Post reply

Help with MAX/TOP (Not sure)

  • April 13, 2011 at 4:51 pm

    #1311378

    You could either use a subquery to get the max effective date per code and join it back to the original table or you could use the ROW_NUMBER approach.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • April 15, 2011 at 7:23 am

    #1312123

    Seomething like This.

    (Please note that I havn't tested this and it might need tweaking).

    SELECT CODE, TYPE, CLASS, EFFECTIVE_DATE, PRICE, CATEGORY, PRICE, MARKED_UP_PRICE

    FROM (

    SELECT CODE, TYPE, CLASS, EFFECTIVE_DATE, PRICE, CATEGORY, PRICE, PRICE * 1.25 AS MARKED_UP_PRICE,

    ROW_NUMBER() OVER(PARTITION BY CODE, TYPE, CLASS ORDER BY CODE, TYPE, CLASS, EFFECTIVE_DATE DESC)AS ROW_INSTANCE

    FROM TABLE_A

    ) AS DERIVED_TABLE_A

    WHERE DERIVED_TABLE_A.ROW_INSTANCE = 1

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

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