Ooops! Appologies to all for being sooo stupid! I thought the order by was applied before the WHERE and if ordering in DESC order for example < would mean greater than and so on in the where clause because I assumed < meant it would appear before in the returned order.
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 POstgres and I just wasted a day being dumb (guess I should get more than 2 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 > > <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html