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.
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?
Thanks,
On Thursday 13 Feb 2003 4:17 pm, Tom Lane wrote:
> Nicholas Allen <nallen@freenet.co.uk> writes:
> > I then commented out the line just to see if this would fix the problem.
> > Then I rebuilt it started the server up and connected. I performed the
> > count query as I described bfore and it worked perfectly! It did exactly
> > what I wanted! Now obviously the code was in there for some reason but it
> > seems that it is not necessary to check it in this case. There must be a
> > bug here surely. MySQL also allows it so I don't think it is invalid SQL
> > on my part. And the fact it works perfectly if I disable this check is
> > very promising.
>
> There is no bug here, or wasn't until you broke it. The given query is
> illegal according to the SQL standard (MySQL is a fairly unreliable
> guide to standard behavior :-(). It seems quite useless anyway:
> "SELECT count(*)" will return exactly one row, so what's the meaning of
> putting an ORDER BY clause on it?
>
> > Now if I execute this (note only difference is change from * to
> > count(*)):
> >
> > select count(*) FROM vu_tbl_user_all_s WHERE s_surname < 'Asurname' or
> > (s_surname = 'Asurname' and s_alias <= 'CISX' and s_loginid <=
> > 'Loginid8') ORDER BY s_surname, s_loginid;
> >
> > I get this:
> >
> > ERROR: Attribute vu_tbl_user_all_s.s_surname must be GROUPed or used in
> > an aggregate function
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
