On Thu, 13 Feb 2003, Nicholas Allen wrote:
> Because the WHERE clause is directly affected by the ORDER BY clause. If you
> leave out the order by clause then the row count will be completely different
> and therefore wrong. The ORDER BY clause is just as important as the WHERE
> clause when counting rows. It should be possible to get a count for the rows
> for any query that can be done which can return row data as I understand it.
WHERE effectively occurs before ORDER BY, so unless it's an order by in a
subselect I don't think the where clause can be affected by the ORDER BY
legally.
select count(*) from foo where a<3 order by a;
and
select count(*) from foo where a<3;
must give the same results AFAICT.
> I have tried to find a definition for SQL SELECT command but everywhere I have
> looked so far makes no mention of this being invalid SQL syntax. Can you let
> me know where you got this information?
I think this may follow from the syntax rules of the general select from
a strict reading of SQL92 20.2 and 9.7 (which implies to me that any
form of select a from table order by b; would be an extension) or a looser
reading of syntax rule 7 in 9.7 which disallows value expressions
referencing a column that wasn't specified in a set function specification
for non-grouped tables.