Re: Question on Structuring Product Attributes

From: Lennart Jonsson <erik.lennart.jonsson_at_gmail.com>
Date: Mon, 11 Feb 2013 08:15:02 +0100
Message-ID: <kfa5oo$k8e$1_at_dont-email.me>


On 02/11/2013 01:38 AM, James K. Lowden wrote:
> On Sat, 09 Feb 2013 19:36:01 +0100
> Lennart Jonsson <erik.lennart.jonsson_at_gmail.com> wrote:
>

>> My understanding is that a query is - logically - evaluated in the
>> following order:
>>
>> 1. FROM (JOIN)
>> 2. WHERE (ON)
>> 3. GROUP BY -- irrelevant here
>> 4. HAVING   -- irrelevant here
>> 5. SELECT (Projection)
>> 6. ORDER BY

>
> Except #6. ORDER BY, strictly speaking, returns a cursor, not a
> virtual table. Logically, it's a post-processor for the SELECT
> statement.

Agreed

> That's why it can't be included in subqueries.
>

Wasn't this introduced in SQL-2011 (perhaps even SQL-2008)? Looking at the draft 6IWD6-02-Foundation-2011-01.pdf (can be found at http://www.wiscorp.com/SQLStandards.html link http://www.wiscorp.com/sql20nn.zip)

7.13 <query expression>:
[...]
<query expression> ::=

[ <with clause> ] <query expression body>
[ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
[...]

 <query expression body> ::=
<query term>
| <query expression body> UNION [ ALL | DISTINCT ]
[ <corresponding spec> ] <query term>
| <query expression body> EXCEPT [ ALL | DISTINCT ]
[ <corresponding spec> ] <query term>

<query term> ::=
<query primary>
| <query term> INTERSECT [ ALL | DISTINCT ] [ <corresponding spec> ] <query primary> <query primary> ::=
<simple table>

| <left paren> <query expression body>
[ <order by clause> ] [ <result offset clause> ] [ <fetch first clause>
] <right

paren>

and the conformance rule (page 420):

  1. Without Feature F851, “<order by clause>in subqueries”, in conforming SQL language, a <query expression> contained in another <query expression> shall not immediately contain an <order by clause>.

it is my impression that you actually can order by in a sub clause. From a logical point of view it doesn’t make much sense, but I suspect that the construction relates to "fetch first ...".

FWIW, I find it rather difficult to to interpret the document, so it might be a misunderstanding on my behalf.

Cheers
/Lennart

[...] Received on Mon Feb 11 2013 - 08:15:02 CET

Original text of this message