ORDER BY (Entity SQL)

  • [アーティクル]

Specifies the sort order used on objects returned in a SELECT statement.

[ ORDER BY 
   {
            order_by_expression [SKIP n] [LIMIT n]
      [ COLLATE collation_name ]
      [ ASC | DESC ]
   }
   [ ,…n ] 
]

Arguments

  • order_by_expression
    Any valid query expression specifying a property on which to sort. Multiple sort expressions can be specified. The sequence of the sort expressions in the ORDER BY clause defines the organization of the sorted result set.
  • COLLATE {collation_name}
    Specifies that the ORDER BY operation should be performed according to the collation specified in collation_name. COLLATE is applicable only for string expressions.
  • ASC
    Specifies that the values in the specified property should be sorted in ascending order, from lowest value to highest value. This is the default.
  • DESC
    Specifies that the values in the specified property should be sorted in descending order, from highest value to lowest value.
  • LIMIT n
    Only the first n items will be selected.
  • SKIP n
    Skips the first n items.

Remarks

The ORDER BY clause is logically applied to the result of the SELECT clause. The ORDER BY clause can reference items in the select list by using their aliases. The ORDER BY clause can also reference other variables that are currently in-scope. However, if the SELECT clause has been specified with a DISTINCT modifier, the ORDER BY clause can only reference aliases from the SELECT clause.

SELECT c AS c1 FROM cs AS c ORDER BY c1.e1, c.e2

Each expression in the ORDER BY clause must evaluate to some type that can be compared for ordered inequality (less than or greater than, and so on). These types are generally scalar primitives such as numbers, strings, and dates. RowTypes of comparable types are also order comparable.

If your code iterates over an ordered set, other than for a top-level projection, the output is not guaranteed to have its order preserved.

-- In the following sample, order is guaranteed to be preserved:
SELECT C1.FirstName, C1.LastName
        FROM AdventureWorks.Contact as C1
        ORDER BY C1.LastName

-- In the following query ordering of the nested query is ignored.
SELECT C2.FirstName, C2.LastName
    FROM (SELECT C1.FirstName, C1.LastName
        FROM AdventureWorks.Contact as C1
        ORDER BY C1.LastName) as C2

To have an ordered UNION, UNION ALL, EXCEPT, or INTERSECT operation, use the following pattern:

SELECT ...
FROM ( UNION/EXCEPT/INTERSECT operation )
ORDER BY ...

Restricted keywords

The following keywords must be enclosed in quotation marks when used in an ORDER BY clause:

  • CROSS

  • FULL

  • KEY

  • LEFT

  • ORDER

  • OUTER

  • RIGHT

  • ROW

  • VALUE

Ordering Nested Queries

In the Entity Framework, a nested expression can be placed anywhere in the query; the order of a nested query is not preserved.

-- The following query will order the results by the last name.
SELECT C1.FirstName, C1.LastName
        FROM AdventureWorks.Contact as C1
        ORDER BY C1.LastName

-- In the following query, ordering of the nested query is ignored.
SELECT C2.FirstName, C2.LastName
    FROM (SELECT C1.FirstName, C1.LastName
        FROM AdventureWorks.Contact as C1
        ORDER BY C1.LastName) as C2

Example

The following Entity SQL query uses the ORDER BY operator to specify the sort order used on objects returned in a SELECT statement. The query is based on the AdventureWorks Sales Model. To compile and run this query, follow these steps:

  1. Follow the procedure in How to: Execute a Query that Returns StructuralType Results (EntityClient).

  2. Pass the following query as an argument to the ExecuteStructuralTypeQuery method:

SELECT VALUE p FROM AdventureWorksEntities.Product 
    AS p order by p.ListPrice

The output is shown below:

ProductID: 1
Name: Adjustable Race
ProductNumber: AR-5381
MakeFlag: False
ProductID: 2
Name: Bearing Ball
ProductNumber: BA-8327
MakeFlag: False
ProductID: 3
Name: BB Ball Bearing
ProductNumber: BE-2349
MakeFlag: True
ProductID: 4
Name: Headset Ball Bearings
ProductNumber: BE-2908
MakeFlag: False
ProductID: 316
Name: Blade
ProductNumber: BL-2036
MakeFlag: True
...

See Also

Reference

SKIP (Entity SQL)
LIMIT (Entity SQL)
TOP (Entity SQL)

Concepts

Query Expressions (Entity SQL)
Entity SQL Reference