locked
ordering RRS feed

  • Question

  • I have the values

    20a

    20a1

    20a2

    20b

    20c

    20

    22

    24

    and I want them to be ordered in this order, How can i achieve this

    Friday, June 7, 2019 3:37 PM

All replies

  • What is your output?

    A Fan of SSIS, SSRS and SSAS

    Friday, June 7, 2019 3:42 PM
  • create table test ( val varchar(20))
    insert into test values('29'),('20a'),('20a1'),('20a2'),('20b'),('20c'),('20'),('22'),('24')
    select * from test
    
    
    --===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
     
    ,processTable as (
        select val, 
    	col1  as Number_removed_val  
    	,Cast(col2 as int)  as letter_removed_val  
    
        from test 
    	Cross Apply (
                select (select C + ''
                from (select N, substring(val, N, 1) C from Nums  
                where N<=datalength(val)) t
                where PATINDEX('%[^0-9]%',C)> 0
                order by N
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
            ) p0 (col1) 
    			Cross Apply (
                select (select C + ''
                from (select N, substring(val, N, 1) C from Nums  
                where N<=datalength(val)) t
                where PATINDEX('%[0-9]%',C)> 0
                order by N
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
            ) p1 (col2)  
     
    ) 
    SELECT   val
    FROM processTable
    Order by Cast(ISNULL(Stuff(val,PATINDEX('%[^0-9]%',val),len(val),'') ,val) as int)
    ,Number_removed_val,letter_removed_val
     
    
    
    drop table test

    Friday, June 7, 2019 3:53 PM
  • Try the following query. i have jumbled the values just to get the sorting.

    create table #temp (value varchar(10))
    
    insert into #temp
    select '20a'
    
    insert into #temp
    select '20a1'
    
    insert into #temp
    select '20a2'
    
    insert into #temp
    select '22'
    
    insert into #temp
    select '24'
    
    insert into #temp
    select '20b'
    
    insert into #temp
    select '20c'
    
    insert into #temp
    select '20'
    
    
    select * from #temp order by PATINDEX('%[^0-9]%',value) desc


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. - Kerobin

    Friday, June 7, 2019 3:57 PM
  • If you want 20 after 20c, you can adjust the query:

    SELECT   val 
    FROM processTable
    Order by Cast(ISNULL(Stuff(val,PATINDEX('%[^0-9]%',val),len(val),'') ,val) as int)
    ,isnull(Number_removed_val,'z'),letter_removed_val

    Friday, June 7, 2019 4:10 PM
  • Hi Saanah,

     

    Please try following script.

     
    IF OBJECT_ID('test') IS NOT NULL drop table  test
    go 
    create table test ( val varchar(20))
    insert into test values
    ('20a'),('20a1'),('20a2'),('20b'),
    ('20c'),('20'),('22'),('24')
    
    ;with cte as (
    select *,
    case when  PATINDEX('%[^0-9]%',val)= 0 
    then val else left(val,PATINDEX('%[^0-9]%',val)-1) end as [val1],
    case when  PATINDEX('%[^0-9]%',val)= 0 
    then 1 else 0 end [val2],
    case when  PATINDEX('%[^0-9]%',val)= 0 
    then null else right(val,len(val)-PATINDEX('%[^0-9]%',val)+1) end as [val3]
    from test )
    select val from cte order by val1,val2,val3
    /*
    val
    --------------------
    20a
    20a1
    20a2
    20b
    20c
    20
    22
    24
    */

     

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 10, 2019 7:31 AM
  • Hi Saanah,

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 28, 2019 9:49 AM
  • >> I have the values <<

    Why did you fail to post DDL? Your request makes no sense because all we have is a silly picture in ASCII. We now have to put it in the table for you, but if you knew what a table was you would remember that tables have no ordering. This is by definition! It's usually covered in the first chapter of any book on RDBMS and SQL. You really need to read such a book before you try to program again

    CREATE TABLE Foobar (foo_string VARCHAR(10) NOT NULL PRIMARY KEY)'
    INSERT INTO Foobar
    VALUES 
    ('20a'), ('20a1'), ('20a2'), 
    ('20b'), ('20c'), ('20'),
    ('22'),
    ('24');

    >> and I want them to be ordered in this order, How can I achieve this?<<

    By using an implicit cursor with complicated logic. Rachel probably gave you the best kludge that's possible with a mess like this. The real answer however is to stop writing databases like this. Can you start over?

    This is one of the problems with badly designed DDL. You probably don't spend a lot of time looking at industry standards or ISO documents, but most encoding schemes are fixed length, and try to avoid mixing alpha and numerics. Instead of just inventing things on the fly a good database person actually designs his data. It's just like a good programmer actually plans his code instead of writing kludges. Ideally, we'd like to be able to write a very simple regular expression to guarantee data integrity.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, June 28, 2019 3:50 PM