Post reply

Ranking on Group by....

  • September 30, 2008 at 8:19 am

    #193352

    Hi Guys.

    Some help please....

    How do i rank records within a group by clause i.e. for each time a group changes i want to reset the ranking?

    For example, i want to achieve the ranking column as shown below:

    LocationProduct IDQtyRanking*

    T0000009780415254816151

    T0000009780415191388132

    T0000009780415360340133

    T0000009780415973243124

    T000000978905702172555

    T000000978041522171916

    T0009629780824709914121

    T000962978084938264242

    T000962978156032436233

    T000962978041512236824

    T000962978081532251125

    T000962978081532262726

    All advise appreciated, thanks.

    Steve

  • September 30, 2008 at 8:34 am

    #878898

    you can do this using the row_number() function;

    SELECT Location, ProductID, Qty,

    ROW_NUMBER() OVER (PARTITION BY Location ORDER BY Location, ProductID) AS NewOrder

    FROM YOURTABLE

  • September 30, 2008 at 8:43 am

    #878902

    steveb (9/30/2008)

    you can do this using the row_number() function;

    SELECT Location, ProductID, Qty,

    ROW_NUMBER() OVER (PARTITION BY Location ORDER BY Location, ProductID) AS NewOrder

    FROM YOURTABLE

    I'm thinking the ranking is by QTY, not productID, but otherwise - agreed.

    SELECT Location, ProductID, Qty,

    ROW_NUMBER() OVER (PARTITION BY Location ORDER BY Location ASC, QTY DESC) AS NewOrder

    FROM YOURTABLE

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • September 30, 2008 at 9:01 am

    #878915

    Thanks guys very helpful

    Steve

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

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