SQL Issue Record

There are a few problems with SQL today:

1. If select DISTINCT is specified, then the items in the ORDER by clause must appear in the select list

Select DISTINCT ID from Toll station order by name//error

Change to:

Select DISTINCT ID, name from toll station order by name//Normal

Alternatively, use GROUP by instead

Note that the field immediately following the distict does not appear to be in the order by

2. The ORDER by clause is not valid in views, inline functions, derived tables, subqueries, and common table expressions unless you also specify TOP or for XML

Search the Internet for answers (from: http://blog.csdn.net/wrm_nancy/article/details/17170115)

In the SQL language, the first processed clause is the FROM clause, although the SELECT statement first appears, but is almost always finally processed.

Each step produces a virtual table that is used as input to the next step. These virtual tables are not available to callers (client applications or external queries). Only the table generated in the last step is returned to the caller. If you do not specify a clause in the query, the corresponding step is skipped. The following is a brief description of each logical step that is applied to SQL Server 2000 and SQL Server 2005.

(8) SELECT (9) DISTINCT (one) <top num> <select list>
(1) from [left_table]
(3) <join_type> join <right_table>
(2) on <join_condition>
(4) WHERE <where_condition>
(5) GROUP by <group_by_list>
(6) with <cube | Rollup>
(7) having
(Ten) ORDER by <order_by_list>

Introduction to the Logical query processing phase

2. From : performs a cartesian product (Cartesian product) (cross join) on the first two tables in the FROM clause, generating a virtual table VT1
4. On : apply an on filter to VT1. Only those lines that make <join_condition> true are inserted into the VT2.
6. OUTER (Join): If OUTER join is specified (relative to cross join or (INNER join), the reserved table (preserved table: Left outer join marks the left table as a reserved table, right outer join marks the right table as a reserved table, A full outer join that marks two tables as a reserved table) does not find a matching row to be added to VT2 as an outer row, generating VT3. If the FROM clause contains more than two tables, repeat steps 1 through 3 for the result table and the next table that were generated for the previous join until all the tables have been processed.
8. Where: apply a where filter to VT3. Only rows that make <where_condition> true are inserted into the VT4.
10. GROUP By: groups the rows in VT4 by the list of columns in the GROUP BY clause, generating VT5.
12. cube| ROLLUP: Inserts a Hyper-group (suppergroups) into VT5, generating VT6.
14. Having : apply a having filter to VT6. Only groups that make
16. Select: processes the select list, producing VT8.
18. DISTINCT: removes duplicate rows from VT8, resulting in VT9.
20. ORDER BY: generates a cursor (VC10) by sorting the rows in VT9 by the list of columns in the ORDER by clause.
22. TOP: selects the specified number or scale of rows from the beginning of the VC10, generates the table VT11, and returns the caller.

Note: Step 10, sort the rows returned by the column list in the ORDER BY clause, and return the cursor VC10. This step is the first and only step you can use the column aliases in the select list. This step differs from the other step in that it does not return a valid table, but instead returns a cursor. SQL is based on the set theory. The collection does not pre-order its rows, it is only a logical collection of members, and the order of the members is irrelevant. A query that sorts a table can return an object that contains rows organized in a specific physical order. ANSI calls this object a cursor. Understanding this step is the basis for a proper understanding of SQL.

Because this step does not return a table (instead of returning a cursor), a query that uses the ORDER BY clause cannot be used as a table expression. Table expressions include: views, inline table-valued functions, subqueries, derived tables, and common expressions. Its result must be returned to the client application that expects to get the physical record. For example, the following derived table query is invalid and produces an error:

SELECT * FROM (select Orderid,customerid from Orders order by OrderID) as D

The following view also generates an error

Create VIEW My_viewasselect *from Ordersorder by OrderID

In SQL, a query with an ORDER BY clause is not allowed in a table expression, but there is an exception in T-SQL (apply top option).

So remember, don't assume any particular order for the rows in the table. In other words, do not specify an ORDER BY clause unless you are sure you want to order rows. Sorting is a cost, and SQL Server needs to perform an ordered index scan or use the sort runner.

SQL Issue Record