Post reply

How to rank the records

  • November 22, 2020 at 4:44 am

    #3812624

    I am trying to figure how to create rank column in my query with the below logic.

    I need to group by item category and then rank them by ordbo. Any help appreciated

     

    group by

    Attachments:
    You must be logged in to view attached files.

  • November 22, 2020 at 5:20 am

    #3812635

    Most folks here won't touch an Excel file. Make life easy on everyone and post the setup script for your question... something like this:

    use tempdb;
    go

    CREATE TABLE SalesTable (
    SNO int
    ,OrdNo int
    ,AccountNo int
    ,ItemCategory varchar(20)
    ,SalesPersonID int
    , rnk tinyint
    );
    GO
    INSERT INTO Salestable (sno, ordno, AccountNo, ItemCategory, SalespersonID, rnk)
    VALUES (2,77,123,'item1',5,1)
    ,(2,77,123,'item1',5,1)
    ,(2,77,123,'item1',5,1)
    ,(2,86,22,'item1',2,2)
    ,(2,86,22,'item1',6,2)
    ,(3,99,36,'Item2',5,1)
    ,(3,99,36,'Item2',9,1)
    ,(3,120,225,'Item2',2,2)
    ,(3,120,225,'Item2',6,2);

    -- I need to group by item category and then rank them by ordbo
    SELECT sno, ordno, accountno, itemCategory
    , grp = rank() over (partition by itemCategory order by ordno)
    FROM SalesTable;

    Not totally sure that's what you wanted, but at least now you're closer to an answer... but the short answer is to use a windowing function to do it.

  • November 22, 2020 at 6:08 pm

    #3812747

    Thanks SSCU guru point taken.

    I ran the query and it s returning

    rank1

     

    What I am looking for is , I want to rank orderno within each item category

     

    2020_11_22_12_06_22_groupby.xlsx_Exceltday

    Any help appreciated

  • November 22, 2020 at 7:02 pm

    #3812763

    Use DENSE_RANK instead of RANK in pietlinden's code.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 22, 2020 at 7:29 pm

    #3812769

    Awesome

  • November 22, 2020 at 7:33 pm

    #3812770

    Angela4eva wrote:

    Awesome

    The question now is... do you know how and why it works?

    https://www.google.com/search?dense_rank+sql+server

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 22, 2020 at 10:12 pm

    #3812797

    Yes Sir, Dense rank does skip the consecutive numbers.

Viewing 7 posts - 1 through 6 (of 6 total)

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