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

[Former Member]

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?

[chris_keating]

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.

[former_member428492 · Accepted solution]

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.