X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 6EC9E474E5C
	for <pgsql-sql@postgresql.org>; Thu, 13 Feb 2003 14:40:29 -0500 (EST)
Received: from email02.aon.at (WARSL402PIP7.highway.telekom.at [195.3.96.94])
	by postgresql.org (Postfix) with SMTP id 019F4474E42
	for <pgsql-sql@postgresql.org>; Thu, 13 Feb 2003 14:40:28 -0500 (EST)
Received: (qmail 401950 invoked from network); 13 Feb 2003 19:40:26 -0000
Received: from m170p016.dipool.highway.telekom.at (HELO cantor)
	([62.46.11.48]) (envelope-sender <mkoi-pg@aon.at>)
	by qmail2rs.highway.telekom.at (qmail-ldap-1.03) with SMTP
	for <nallen@freenet.co.uk>; 13 Feb 2003 19:40:26 -0000
From: Manfred Koizar <mkoi-pg@aon.at>
To: Nicholas Allen <nallen@freenet.co.uk>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
	Peter Childs <blue.dragon@blueyonder.co.uk>, pgsql-sql@postgresql.org
Subject: Re: Debugging postmaster to fix possible bug in Postgres? Followup to
	"How do you select
Date: Thu, 13 Feb 2003 20:39:38 +0100
Message-ID: <44sn4vgisulljq45qo35bug12sed1ll3ce@4ax.com>
References: <Pine.LNX.4.44.0302131019020.6771-100000@RedDragon.Childs>
	<200302131139.05868.nallen@freenet.co.uk>
	<13266.1045149477@sss.pgh.pa.us>
	<200302131828.50026.nallen@freenet.co.uk>
In-Reply-To: <200302131828.50026.nallen@freenet.co.uk>
X-Mailer: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/295
X-Sequence-Number: 11693

On Thu, 13 Feb 2003 18:28:50 +0100, Nicholas Allen
<nallen@freenet.co.uk> wrote:
>Because the WHERE clause is directly affected by the ORDER BY clause.

No, it's not (at least in your query).

> If you 
>leave out the order by clause then the row count will be completely different 
>and therefore wrong.

I must be missing something.  Please give an example.

> 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.

If you have a set of numbers, say {1, 9, 5, 3, 7}, and want to know
how many elements of the set are <= 7 (SELECT COUNT(*) FROM s WHERE
n<=7), you simply look a each element - no matter in what order - and
increase your counter, if the element satifies your condition.  I
can't see how you get different numbers when you count {1, 3, 5, 7},
{1, 5, 3, 7} or any other permutation.

>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?

SQL92 says:
    <direct select statement: multiple rows> ::=
         <query expression> [ <order by clause> ]
    [...]
    3) Let T be the table specified by the <query expression>.

    4) If ORDER BY is specified, then each <sort specification> in the
       <order by clause> shall identify a column of T.

Servus
 Manfred