X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B6FDE474E4F for ; Thu, 13 Feb 2003 05:06:00 -0500 (EST) Received: from localhost.localdomain (B40d7.pppool.de [213.7.64.215]) by postgresql.org (Postfix) with ESMTP id 1711E474E42 for ; Thu, 13 Feb 2003 05:05:58 -0500 (EST) Received: from localhost.localdomain (localhost.localdomain [127.0.0.1]) by localhost.localdomain (8.11.6/8.11.6) with ESMTP id h1DA45701975; Thu, 13 Feb 2003 11:04:06 +0100 Content-Type: text/plain; charset="iso-8859-1" From: Nicholas Allen To: Peter Childs Subject: Re: Possible bug in Postgres? Followup to "How do you select Date: Thu, 13 Feb 2003 11:04:05 +0100 User-Agent: KMail/1.4.1 Cc: pgsql-sql@postgresql.org References: In-Reply-To: MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200302131104.05086.nallen@freenet.co.uk> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/282 X-Sequence-Number: 11680 Sorry, I don't think you understand. I HAVE surnames and I have data that i= s returned by the SELECT query. When I execute this: select * FROM vu_tbl_user_all_s WHERE s_surname < 'Asurname' or=20 (s_surname =3D 'Asurname' and s_alias <=3D 'CISX' and s_loginid <=3D 'Login= id8')=20 ORDER BY s_surname, s_loginid; I get this: s_alias | s_surname | s_name | s_midname | s_loginid | b_disabled | s= _4eyestype | s_usertype | b_hasvaluation | d_dob | s_emai= l | s_phone | s_phone_mb ---------+-------------+---------+-----------+-----------+------------+----= -------------+--------------------+----------------+------------+----------= -----+---------+------------ CISX | 3 A Surname | 2aname | | Loginid3 | | No = restrictions | CISX Administrator | | 1966-12-12 | = | | CISX | A Asurname | A Aname | | Loginid2 | | No = restrictions | CISX Administrator | | 1966-12-12 | = | | CISX | Asurname | Aname | | Demolish3 | | No = restrictions | CISX Administrator | | 1966-12-12 | = | | CISX | Asurname | Aname | | Demolish4 | | No = restrictions | CISX Administrator | | 1966-12-12 | = | | CISX | Asurname | Aname | | Demolish5 | | No = restrictions | CISX Administrator | | 1966-12-12 | = | | CISX | Asurname | Aname | | Demolish6 | | No = restrictions | CISX Administrator | | 1966-12-12 | = | | CISX | Asurname | Aoname | | Lloginid | | No = restrictions | CISX Administrator | | 1966-12-12 | = | | CISX | Asurname | Aname | | Loginid | | No = restrictions | CISX Administrator | | | asdf@asdf= .com | | CISX | Asurname | Aname | | Loginid8 | | No = restrictions | CISX Administrator | | 1966-12-12 | = | | (9 rows) 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=20 (s_surname =3D 'Asurname' and s_alias <=3D 'CISX' and s_loginid <=3D 'Login= id8')=20 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 My point is if there is data to be returned in a SELECT statement then post= gres should be able to count the number of rows that would be returned with= out actually returning me the data for the rows. Surely any SELECT statement that returns data can also be used to count dat= a that would be returned?=20 Thanks again! On Thursday 13 Feb 2003 10:58 am, Peter Childs wrote: > On Thu, 13 Feb 2003, Nicholas Allen wrote: > > But I'm not returning the surnames. I just execute this query (sorry I > > forgot to put it in my last email): > > > > select count(*) FROM vu_tbl_user_all_s WHERE s_surname < 'Asurname' or > > (s_surname =3D 'Asurname' and s_alias <=3D 'CISX' and s_loginid <=3D > > 'Loginid8') ORDER BY s_surname, s_loginid; > > Excuse me but why are you ordering a SELECT count(*) statement. > The ORDER BY clause is probably why you it wants to group it because > postgres whats somthing to order. > > > So all I am returning is the count right? If I execute the query using * > > instead of count(*) to actually see the rows it returns the rows as > > expected upto the row I was interested in. I would assume if postgres c= an > > execute a query to give me row data it should also be able to execute t= he > > query and count the rows without actually giving me the row data when I > > use count(*). > > > > I have tried this on MySQL and it works just not on Postgres so it > > doesn't seem to be something I am doing wrong. Which is why I thought it > > must be a bug in Postgres. > > I can't see the bug its saying order by surname so it looks for > some surnames to order by finds none so complains. If there is any bug > its likly to be that this works in MySQL. :) > > > Am I not missing something here. Is there something wrong with the synt= ax > > of the above query? > > Sorry but don't try sorting unless you have somthing to sort. > > > Thjanks again for your help! I really appreciate it. > > Peter Childs > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly