Skip to Content
avatar image
Former Member

How to alter table to change the order of rows by certain values

How can i alter this table and order them by LISTORDER for example first to come row that has the listorder of 5 then row with listorder of 3 then listorder 7, or any custom order that i need?

1.jpg (62.2 kB)
Add comment
10|10000 characters needed characters exceeded

  • The only guaranteed order for a resultset is by using an ORDER BY clause. You could use a column or key,ordinal table to define the sort order and use those in the ORDER BY. This may work but as tables grow, it may be problematic to maintain the ordinals.

  • Get RSS Feed

1 Answer

  • Best Answer
    Apr 26, 2017 at 09:37 AM
    SQL Anywhere allows expressions in the ORDER BY, 
    and the CASE expression can be used here.
    
    
    CREATE TABLE t ( 
       FIRST_NAME  VARCHAR ( 10 ),
       POSITION    VARCHAR ( 20 ),
       LISTORDER   INTEGER );
    
    
    INSERT t VALUES ( 'Dejah', 'CEO', 1 );
    INSERT t VALUES ( 'John', 'DEVELOPER', 2 );
    INSERT t VALUES ( 'Tosho', 'SALES ASSOCIATE', 3 );
    INSERT t VALUES ( 'Mike', 'ACCOUNT MANAGER', 4 );
    INSERT t VALUES ( 'Cacko', 'MARKETING', 5 );
    INSERT t VALUES ( 'Bapco', 'IT', 6 );
    INSERT t VALUES ( 'Blazo', 'SALES', 7 );
    INSERT t VALUES ( 'Risto', 'ACCOUNTANT', 8 );
    COMMIT;
    
    
    SELECT * FROM t
    ORDER BY CASE LISTORDER
                WHEN 1 THEN 4
                WHEN 2 THEN 5
                WHEN 3 THEN 2
                WHEN 4 THEN 6
                WHEN 5 THEN 1
                WHEN 6 THEN 8
                WHEN 7 THEN 3
                WHEN 8 THEN 9
                ELSE 99999
             END CASE;
    
    
    FIRST_NAME POSITION               LISTORDER 
    ---------- -------------------- ----------- 
    Cacko      MARKETING                      5 
    Tosho      SALES ASSOCIATE                3 
    Blazo      SALES                          7 
    Dejah      CEO                            1 
    John       DEVELOPER                      2 
    Mike       ACCOUNT MANAGER                4 
    Bapco      IT                             6 
    Risto      ACCOUNTANT                     8 
    
    
    You could also make it more powerful/flexible/complicated by 
    creating a separate table that defines [ LISTORDER, order-by-order ] 
    pairs and use that in a join.
    
    
    You probably don't want to actually ALTER the base table definition.
    
    
    
    Add comment
    10|10000 characters needed characters exceeded