locked
Group result with hyphens not correct RRS feed

  • Question

  • User1159132868 posted

    Hi

    I am creating a report in SSRS with column name "Rm No" which look like this when run (see below), the sort order is the way they want it to look on the report

    18-1

    18-2

    18-3

    18-9

    18-10

    18-19

    18-20

    However  when I group by "Rm No" the result is not the same as expected. The result like ( see below) 

    18-1

    18-10

    18-19

    18-2

    etc...

    Anyone know how to get this grouping result to group in the order they are looking for - Grouping order 18-1, 18-2, 18-3, 18-9 ... etc..

    The database field is VARCHAR, and when the query is executed it display the data in the correct sort order.

    Any help will be greatly appreciated

    Thanks  

    Monday, July 31, 2017 5:07 PM

All replies

  • User753101303 posted

    Hi,

    This is the expected alphanumeric order ie (sorting text is done starting from the leftmost character). You could use 18-01 or numeric values. Processing text values to "fix" the order (such as findind - and sorting each part once converted to numeric) would be my very last choice.

    Monday, July 31, 2017 6:18 PM
  • User1159132868 posted

    Thanks fro responding. really appreciate it.

    Wednesday, August 2, 2017 1:16 PM
  • User991499041 posted

    Hi Hogie503,

    Anyone know how to get this grouping result to group in the order they are looking for - Grouping order 18-1, 18-2, 18-3, 18-9 ... etc..

    The database field is VARCHAR, and when the query is executed it display the data in the correct sort order.

    As the rows contains the multiple hyphens solved by using the below query

    DECLARE @test TABLE
    ([Rm No] VARCHAR(50))
    
    INSERT INTO @test values
    ('18-10'),
    ('18-19'),
    ('18-20'),
    ('18-1'),
    ('18-2'),
    ('18-3'),
    ('18-9')
    
    SELECT * FROM @test ORDER BY CONVERT(INT,RIGHT([Rm No], CHARINDEX( '-', REVERSE( [Rm No] ) + '-' ) - 1))

    Regards,

    zxj

    Thursday, August 10, 2017 5:19 AM