CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3652154
user3652154

Reputation:

Strange issue with the Order By --SQL

Few days ago I came across a strange problem with the Order By , While creating a new table I used 

Select - Into - From and Order By (column name)

and when I open that table see tables are not arranged accordingly.

I re-verified it multiple times to make sure I am doing the right thing.

One more thing I would like to add is till the time I don't use INTO, I can see the desired result but as soon as I create new table, I see there is no Order for tht column. Please help me !

Thanks in advance.. Before posting the question I did research for 3 days but no solution yet

SELECT 
    [WorkOrderID], [ProductID], [OrderQty], [StockedQty] 
INTO 
    [AdventureWorks2012].[Production].[WorkOrder_test] 
FROM 
    [AdventureWorks2012].[Production].[WorkOrder] 
ORDER BY
    [StockedQty]

Upvotes: 0

Views: 81

Answers (2)

Deepshikha
Deepshikha

Reputation: 10264

Firstly T-SQL is a set based language and sets don't have orders. More over it also doesn't mean serial execution of commands i.e, the above query is not executed in sequence written but the processing order for a SELECT statement is as:

1.FROM
2.ON
3.JOIN
4.WHERE
5.GROUP BY
6.WITH CUBE or WITH ROLLUP
7.HAVING
8.SELECT
9.DISTINCT
10.ORDER BY

Now when you execute your query without into selected column data gets ordered based on the condition specified in 'Order By' clause but when Into is used format of new_table is determined by evaluating the expressions in the select list.(Remember order by clause has not been evaluated yet). The columns in new_table are created in the order specified by the select list but rows cannot be ordered. It's a limitation of Into clause you can refer here:

Specifying an ORDER BY clause does not guarantee the rows are inserted 
in the specified order.

Upvotes: 0

TomTom
TomTom

Reputation: 62093

SQL 101 for beginners: SELECT statements have no defined order unless you define one.

When i open that table

That likely issues a SELECT (TOP 1000 IIFC) without order.

While creating a new table i used Select - Into - From and Order By (column name)

Which sort of is totally irrelevant - you basically waste performance ordering the input data.

You want an order in a select, MAKE ONE by adding an order by clause to the select. The table's internal order is by clustered index, but an query can return results in any order it wants. Fundamental SQL issue, as I said in the first sentence. Any good book on sql covers that in one of the first chapters. SQL uses a set approach, sets have no intrinsic order.

Upvotes: 5

Related Questions