Post reply

Sql force me to include Row_Number() in group by

  • September 15, 2013 at 12:07 am

    #283204

    Hi, this is my table structure:

    CREATE TABLE #table1 (ID INT, Col1 NVARCHAR(1), Col2 INT);

    INSERT INTO #table1 VALUES(1,'B',100);

    INSERT INTO #table1 VALUES(2,'A',200);

    INSERT INTO #table1 VALUES(3,'B',300);

    INSERT INTO #table1 VALUES(4,'C',400);

    I want to have SUM(Col2) GROUPED BY(Col1) and a ROW_NUMBER() ORDERED BY (ID)

    Here is my query:

    WITH CTE(Col1, Col2, Row_Num) AS (

    SELECT Col1 ,SUM(Col2), ROW_NUMBER() OVER(ORDER BY (ID))

    FROM #table1

    GROUP BY Col1, ID -- here SQL force me to include ID and it generate incorrect output

    )

    SELECT * FROM CTE

    Here is my output:

    col1,col2,Row_Num

    -------------------

    B,100,1

    A,200,2

    B,300,3

    C,400,4

    But I want this output:

    col1,col2,Row_Num

    -------------------

    B,400,1

    A,200,2

    C,400,3

    Thank you for help

    ___________________________________
    Computer Enterprise Masoud Keshavarz
    I don't care about hell.
    If I go there I've played enough Diablo to know how to fight my way out.

  • September 15, 2013 at 1:27 am

    #1650262

    After several hours of thinking if seems using min(ID) solve problem

    WITH CTE(Col1, Col2, Row_Num) AS (

    SELECT Col1 ,SUM(Col2), ROW_NUMBER() OVER(ORDER BY (MIN(ID)))

    FROM #table1

    GROUP BY Col1

    )

    SELECT * FROM CTE

    ___________________________________
    Computer Enterprise Masoud Keshavarz
    I don't care about hell.
    If I go there I've played enough Diablo to know how to fight my way out.

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

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