locked
How to sort numbers that are in varchar type under GROUP BY GROUPING SETS in SQL Server? RRS feed

  • Question

  • I have a column called UPC (varchar type) that only contain numbers. I also have a GROUP BY GROUPING SETS on UPC.

    Currently, it's sorted as varchar, which result the numbers not sorted from smallest to largest.

    GROUP BY 
    GROUPING SETS 
    (
        (
            [UPC]
        )
        ,()     
    )

    enter image description here

    If I use convert the UPC from varchar to bigint, the numbers will be sorted alphabetically but then my last row generated from GROUPING SETS will move from last to first.

    GROUP BY 
    GROUPING SETS 
        (
            (
                [UPC]
            )
            ,()     
        )
    
    ORDER BY convert(bigint, UPC)

    enter image description here

    Is there a way to make my "grand total" move to the last row and still have my numbers sorted alphabetically? I'm guessing i might need to use GROUPING_ID?


    • Edited by dchencm Friday, August 22, 2014 4:41 PM
    Friday, August 22, 2014 4:05 PM

Answers

  • yes

    you need to

    GROUP BY 
    GROUPING SETS 
        (
            (
                [UPC]
            )
            ,()     
        )
    
    ORDER BY CASE WHEN GROUPING_ID((UPC)) = 1 THEN 2 ELSE 1 END,convert(bigint, UPC)


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by dchencm Friday, August 22, 2014 5:18 PM
    Friday, August 22, 2014 4:45 PM
    Answerer

All replies

  • yes

    you need to

    GROUP BY 
    GROUPING SETS 
        (
            (
                [UPC]
            )
            ,()     
        )
    
    ORDER BY CASE WHEN GROUPING_ID((UPC)) = 1 THEN 2 ELSE 1 END,convert(bigint, UPC)


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by dchencm Friday, August 22, 2014 5:18 PM
    Friday, August 22, 2014 4:45 PM
    Answerer
  • Thank you. It works.
    Could you briefly explain the "1 then 2 else 1"?
    I'm still a bit confused about the logic.

    Thanks.

    Friday, August 22, 2014 5:21 PM