X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 19AF2474E61
	for <pgsql-sql@postgresql.org>; Thu, 13 Feb 2003 15:53:36 -0500 (EST)
Received: from localhost.localdomain (Bb889.pppool.de [213.7.184.137])
	by postgresql.org (Postfix) with ESMTP id D73C7474E42
	for <pgsql-sql@postgresql.org>; Thu, 13 Feb 2003 15:53:33 -0500 (EST)
Received: from localhost.localdomain (localhost.localdomain [127.0.0.1])
	by localhost.localdomain (8.11.6/8.11.6) with ESMTP id h1DKpG712438;
	Thu, 13 Feb 2003 21:51:17 +0100
Content-Type: text/plain;
  charset="iso-8859-1"
From: Nicholas Allen <nallen@freenet.co.uk>
To: Manfred Koizar <mkoi-pg@aon.at>
Subject: Re: Debugging postmaster to fix possible bug in Postgres? Followup to
	"How do you select
Date: Thu, 13 Feb 2003 21:51:15 +0100
User-Agent: KMail/1.4.1
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
	Peter Childs <blue.dragon@blueyonder.co.uk>, pgsql-sql@postgresql.org
References: <Pine.LNX.4.44.0302131019020.6771-100000@RedDragon.Childs>
	<200302131828.50026.nallen@freenet.co.uk>
	<44sn4vgisulljq45qo35bug12sed1ll3ce@4ax.com>
In-Reply-To: <44sn4vgisulljq45qo35bug12sed1ll3ce@4ax.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200302132151.15664.nallen@freenet.co.uk>
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/298
X-Sequence-Number: 11696

Ooops! Appologies to all for being sooo stupid!

I thought the order by was applied before the WHERE and if ordering in DESC=
=20
order for example < would mean greater than and so on in the where clause=
=20
because I assumed < meant it would appear before in the returned order.=20

Of course the where just selects which values and the order by orders them!

Don't know how I even got confused in the first place. So there's no bug in=
=20
POstgres and I just wasted a day being dumb (guess I should get more than 2=
=20
hours sleep before tackling this kinda stuff!).

Thanks all,

On Thursday 13 Feb 2003 8:39 pm, Manfred Koizar wrote:
> 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 <=3D 7 (SELECT COUNT(*) FROM s WHERE
> n<=3D7), 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. C=
an
> > you let me know where you got this information?
>
> SQL92 says:
>     <direct select statement: multiple rows> ::=3D
>          <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