How to rank the records

  • 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.
  • 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.

  • 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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Awesome

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Yes Sir, Dense rank does skip the consecutive numbers.

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

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