SAP SQL Anywhere

Question

Execute the following SQL. When executed in SA 16, the sortorder is not retained. Is there a work-around to retain the sort order? It needs to be sorted in the SELECT INTO, because the sorting column not will be created in the table.

CREATE TABLE #MyTable(Name VARCHAR(50), SortOrder INT)
INSERT INTO #MyTable SELECT 'b', 2 UNION ALL SELECT 'c', 3 UNION ALL SELECT 'a', 1 UNION ALL SELECT 'e', 5 UNION ALL SELECT 'd', 4

SELECT * INTO #Result FROM #MyTable ORDER BY SortOrder

SELECT * FROM #Result

DROP TABLE #MyTable
DROP TABLE #Result

Accepted Answer

3

In a relational database the order is not guaranteed unless you explicitly state the order. Your second SELECT statement does not contain an ORDER BY clause so the returned order is allowed to be any order.

If you want to get a specific order back you can add an "bymyord INT DEFAULT AUTOINCREMENT" column to the first SELECT statement and then add an "ORDER BY bymyord" in the second SELECT statement.

HTH

permanent link

answered 02 Mar '16, 14:15

Mark Culp
24.9k●10●139●297
accept rate: 41%

1

The #Result table already contains a SortOrder column, no need (in this case) for an autoincrement...

...but your suggestion is worthwhile in many other cases where the original sort order is lost on insert; e.g., when a text file is loaded into a VARCHAR table.

(02 Mar '16, 14:22) Breck Carter

flat view

SAP SQL Anywhere

SELECT * INTO does not retain the ORDER BY clause in SA 16?

Follow this question

Related questions