Skip to Content
0

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

Apr 20, 2017 at 12:16 PM

109

avatar image

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)
10 |10000 characters needed characters left 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.

0
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Breck Carter Apr 26, 2017 at 09:37 AM
1
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.


Share
10 |10000 characters needed characters left characters exceeded