locked
Order By not working RRS feed

  • Question

  • Gurus,

    I am trying to do order by on a calculated field but not getting expected results...

    Column1
    I072
    US024
    I0155
    ILL04
    I055

    Desired Result:
    I 55
    I 72
    I 155
    US 24
    ILL 4

    How do I get this result?

    Thanks in advance.
    Friday, November 12, 2010 4:44 AM

Answers

  • DECLARE @TT TABLE(Column1 VARCHAR(50))
    INSERT @TT VALUES('I072')
    INSERT @TT VALUES('US024')
    INSERT @TT VALUES('I0155')
    INSERT @TT VALUES('ILL04')
    INSERT @TT VALUES('I055')
    SELECT 
    Charformat + ' ' + CAST( NUmberformat as varchar(20)) as COl
    FROM @TT t
    cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as NUmberformat)t1
    ORDER BY LEN(CharFormat),Charformat,NUmberformat
    
    • Marked as answer by kellog1 Friday, November 12, 2010 3:35 PM
    Friday, November 12, 2010 3:22 PM
  • DECLARE @TT TABLE(Column1 VARCHAR(50))
    INSERT @TT VALUES('I072')
    INSERT @TT VALUES('US024')
    INSERT @TT VALUES('I0155')
    INSERT @TT VALUES('ILL04')
    INSERT @TT VALUES('I055')
    
    SELECT 
    Charformat+CAST(Numberformat AS VARCHAR(50)) [Column1]
    FROM @TT t
    cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as Numberformat)t1
    ORDER BY LEN(CharFormat),Charformat,Numberformat
    
    SELECT 
    Charformat+SPACE(1)+CAST(Numberformat AS VARCHAR(50)) [Column1]
    FROM @TT t
    cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as Numberformat)t1
    ORDER BY LEN(CharFormat),Charformat,Numberformat
    
    SELECT 
    Charformat+CAST(Numberformat AS VARCHAR(50)) [Column1]
    FROM @TT t
    cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as Numberformat)t1
    ORDER BY Charformat,Numberformat
    
    SELECT 
    Charformat+SPACE(1)+CAST(Numberformat AS VARCHAR(50)) [Column1]
    FROM @TT t
    cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as Numberformat)t1
    ORDER BY Charformat,Numberformat
    
    
    
    

    Jon
    • Marked as answer by kellog1 Friday, November 12, 2010 3:36 PM
    Friday, November 12, 2010 3:25 PM

All replies

  • Plz post the query you are using...
    ~Manu
    http://sqlwithmanoj.wordpress.com
    Friday, November 12, 2010 4:53 AM
  • hi kellog,

    Yes it was not giving you output b'cos it's combined string and int.try to add sort order column as int and change sort order as you need. 

    Thanks

    Tharindu Dhaneenja


    Tharindu Dhaneenja (http://dhaneenja.blogspot.com)
    Friday, November 12, 2010 5:07 AM
  • Hi, try to use this function :

    CREATE FUNCTION Search_STR (@s varchar(200))
    RETURNS int
    AS
    BEGIN
    declare @i int
    set @i=0
    WHILE @i<=len(@s) and isnumeric(substring(@s,@i,1))=0
    Begin
    set @i=@i+1
    End
    return @i-1
    END
    -----------------------------------------------------------
    declare @TT table(string varchar(50))
    insert @TT values('I072')
    insert @TT values('US024')
    insert @TT values('UZ024')
    insert @TT values('I0155')
    insert @TT values('ILL04')
    insert @TT values('I055')
    select substring(string,1,dbo.Search_STR(string)) as a,cast(substring(string,dbo.Search_STR(string)+1,len(string)-dbo.Search_STR(string)) as int) as b from @TT order by dbo.Search_STR(string),a,b
    --results
    --I 55
    --I 72
    --I 155
    --US 24
    --ILL 4

     

     

     

     


    Best regards
    Friday, November 12, 2010 5:22 AM
  • SELECT LEFT(Col1, 3) +' '+ REPLACE(SUBSTRING(Col1,2,4),'0','') rt
    from Table
    order by rt
    Friday, November 12, 2010 5:24 AM
  • Hi.

     

    Try this please

     

    SELECT LEFT(Col1, 3) +' '+ REPLACE(SUBSTRING(Col1,2,4),'0','') rt
    into #TMP
    from Table
    
    select * FROM #tmp ORDER BY RT
    <br/>
    drop table #TMP
    


    karepa
    Friday, November 12, 2010 5:39 AM
  • Hi, try to use this function :

    CREATE FUNCTION Search_STR (@s varchar(200))
    
    RETURNS int
    
    AS
    
    BEGIN
    
    declare @i int
    
    set @i=0
    
    WHILE @i<=len(@s) and isnumeric(substring(@s,@i,1))=0
    
    Begin
    
    set @i=@i+1
    
    End
    
    return @i-1
    
    END
    
    -----------------------------------------------------------
    
    declare @TT table(string varchar(50))
    
    insert @TT values('I072')
    
    insert @TT values('US024')
    
    insert @TT values('UZ024')
    
    insert @TT values('I0155')
    
    insert @TT values('ILL04')
    
    insert @TT values('I055')
    
    select substring(string,1,dbo.Search_STR(string)) as a,cast(substring(string,dbo.Search_STR(string)+1,len(string)-dbo.Search_STR(string)) as int) as b from @TT order by dbo.Search_STR(string),a,b
    
    --results
    
    --I 55
    
    --I 72
    
    --I 155
    
    --US 24
    
    --ILL 4
    
    

     

     

     

     


    Best regards


    If you want one calculed filed, try :

    select substring(string,1,dbo.Search_STR(string))+' '+cast(cast(substring(string,dbo.Search_STR(string)+1,len(string)-dbo.Search_STR(string)) as int) as varchar(50)) as calculed from @TT order by dbo.Search_STR(string),substring(string,1,dbo.Search_STR(string)),cast(substring(string,dbo.Search_STR(string)+1,len(string)-dbo.Search_STR(string)) as int)
    

     

     


    Best regards
    Friday, November 12, 2010 6:05 AM
  • Not sure why ILL should be after US tho, surely it should come after I.

    DECLARE @TT TABLE(Column1 VARCHAR(50))
    INSERT @TT VALUES('I072')
    INSERT @TT VALUES('US024')
    INSERT @TT VALUES('I0155')
    INSERT @TT VALUES('ILL04')
    INSERT @TT VALUES('I055')
    
    SELECT 
    Column1,LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1),CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT)
    FROM @TT 
    ORDER BY LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1),CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT)
    
    --I055
    --I072
    --I0155
    --ILL04
    --US024
    

    Jon
    Friday, November 12, 2010 8:30 AM
  • Probably  US came before ILL based on lenght... IF the ordering is based on length,then based on number, this is an example

    DECLARE @TT TABLE(Column1 VARCHAR(50))
    INSERT @TT VALUES('I072')
    INSERT @TT VALUES('US024')
    INSERT @TT VALUES('I0155')
    INSERT @TT VALUES('ILL04')
    INSERT @TT VALUES('I055')
    SELECT 
    Column1,Charformat,NUmberformat
    FROM @TT t
    cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as NUmberformat)t1
    ORDER BY LEN(CharFormat),Charformat,NUmberformat
    
    
    Friday, November 12, 2010 8:58 AM
  • Thanks guys for helping me out. But I am looking for one Calculated field only.

    Desired Result Column1 should look like this:

    Column1

    I 55

    I 72

    I 155

    Friday, November 12, 2010 3:16 PM
  • DECLARE @TT TABLE(Column1 VARCHAR(50))
    INSERT @TT VALUES('I072')
    INSERT @TT VALUES('US024')
    INSERT @TT VALUES('I0155')
    INSERT @TT VALUES('ILL04')
    INSERT @TT VALUES('I055')
    SELECT 
    Charformat + ' ' + CAST( NUmberformat as varchar(20)) as COl
    FROM @TT t
    cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as NUmberformat)t1
    ORDER BY LEN(CharFormat),Charformat,NUmberformat
    
    • Marked as answer by kellog1 Friday, November 12, 2010 3:35 PM
    Friday, November 12, 2010 3:22 PM
  • DECLARE @TT TABLE(Column1 VARCHAR(50))
    INSERT @TT VALUES('I072')
    INSERT @TT VALUES('US024')
    INSERT @TT VALUES('I0155')
    INSERT @TT VALUES('ILL04')
    INSERT @TT VALUES('I055')
    
    SELECT 
    Charformat+CAST(Numberformat AS VARCHAR(50)) [Column1]
    FROM @TT t
    cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as Numberformat)t1
    ORDER BY LEN(CharFormat),Charformat,Numberformat
    
    SELECT 
    Charformat+SPACE(1)+CAST(Numberformat AS VARCHAR(50)) [Column1]
    FROM @TT t
    cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as Numberformat)t1
    ORDER BY LEN(CharFormat),Charformat,Numberformat
    
    SELECT 
    Charformat+CAST(Numberformat AS VARCHAR(50)) [Column1]
    FROM @TT t
    cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as Numberformat)t1
    ORDER BY Charformat,Numberformat
    
    SELECT 
    Charformat+SPACE(1)+CAST(Numberformat AS VARCHAR(50)) [Column1]
    FROM @TT t
    cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as Numberformat)t1
    ORDER BY Charformat,Numberformat
    
    
    
    

    Jon
    • Marked as answer by kellog1 Friday, November 12, 2010 3:36 PM
    Friday, November 12, 2010 3:25 PM