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

asked 02 Mar '16, 14:02

Rolle's gravatar image

Rolle
379213142
accept rate: 0%


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%20Culp's gravatar image

Mark Culp
22.9k9129269
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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×5

question asked: 02 Mar '16, 14:02

question was seen: 193 times

last updated: 02 Mar '16, 14:22