X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 68A7A474E5C for ; Wed, 12 Feb 2003 17:23:33 -0500 (EST) Received: from localhost.localdomain (Bb39b.pppool.de [213.7.179.155]) by postgresql.org (Postfix) with ESMTP id 43315474E44 for ; Wed, 12 Feb 2003 17:23:30 -0500 (EST) Received: from localhost.localdomain (localhost.localdomain [127.0.0.1]) by localhost.localdomain (8.11.6/8.11.6) with ESMTP id h1CMLIa06773; Wed, 12 Feb 2003 23:21:21 +0100 Content-Type: text/plain; charset="iso-8859-1" From: Nicholas Allen To: Dmitry Tkach Subject: Possible bug in Postgres? Followup to "How do you select from a table until a condition is met?" Date: Wed, 12 Feb 2003 23:21:18 +0100 User-Agent: KMail/1.4.1 Cc: Bruno Wolff III , pgsql-sql@postgresql.org References: <200302121910.38232.nallen@freenet.co.uk> <200302122153.08970.nallen@freenet.co.uk> <3E4ACE74.109@openratings.com> In-Reply-To: <3E4ACE74.109@openratings.com> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200302122321.18463.nallen@freenet.co.uk> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/265 X-Sequence-Number: 11663 Ok I have decided that this is the best way to do it. Assuming I have a col= umn (or a few columns) which can uniquely define a row I should be able to = select up to a known row with a clever where clause. For example a table co= ntains the following dummy data (the combination of s_alias and s_loginid w= ill be unique for each row): select * FROM vu_tbl_user_all_s ORDER BY s_surname, s_loginid; s_alias | s_surname | s_name | s_midname | s_loginid | b_disabled | = s_4eyestype | s_usertype | b_hasvaluation | d_dob | s_ema= il | 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@asd= f.com | | CISX | Asurname | Aname | | Loginid8 | | No= restrictions | CISX Administrator | | 1966-12-12 | = | | CISX | Asurname | Aname | | Loginid9 | | No= restrictions | CISX Administrator | | 1966-12-12 | = | | CISX | A Surname | Aname | | Loginid4 | | No= restrictions | CISX Administrator | | 1966-12-12 | = | | CISX | Cisx | Cursor | | Systemuser | f | No= restrictions | CISX Administrator | f | 1966-01-23 | = | | CISX | Dazor | Ronald | | Demolish | t | No= restrictions | CISX Administrator | | 1967-12-12 | = | | CISX | Dazor | Ronald | | Demolish2 | f | No= restrictions | CISX Administrator | | 1967-12-12 | = | | (14 rows) I should be able to select up to row 9 (Loginid8)using this query: select * 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; This works and I get the following (expected) output: 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 | Aso it is not the very latest version.name | = | 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) However, if I try to count the records using the count(*) function I get th= e following error. ERROR: Attribute vu_tbl_user_all_s.s_surname must be GROUPed or used in an= aggregate function Surely, if postgres can execute the query it should also be able to count t= he rows that would be returned in the query? Is this a bug in Postgres and = is there a work around? I am using Postgres 7.3. Thanks again! On Wednesday 12 Feb 2003 11:45 pm, Dmitry Tkach wrote: > Nicholas Allen wrote: > >Ok I thought of that but what happens if there is no primary key in the > > table? > > Then tough luck - as you said yourself, since everything else can be > duplicated, the primary key is the only way to > tell for sure which exact row you are talking about. > > >I can probably add primary keys to the table but I didn't design the > > tables and so I have little (but luckily some) say over what columns > > appear in them. What has actually happened is that we have a view on a > > table and the view doesn't return the primary key. I'll try and ask the > > database administrator to add the primary keys. > > > >Thanks for the help though I guess it is the only way to do it. I was ju= st > >hoping there would be a way to do it without a promary key to prevent > > changes to our database views. > > I think you are better off redesigning your interface - even if you do > modify the view, and use those suggestions I mentioned earlier, > it will be slow like hell, unless your table only contains a few hundred > rows, in which case it doesn't matter if you send the whole table to the > client every time, > > Dima > > >On Wednesday 12 Feb 2003 9:37 pm, Bruno Wolff III wrote: > >>On Wed, Feb 12, 2003 at 20:55:21 +0100, > >> > >> Nicholas Allen wrote: > >>>I thought of this but the problem is that there may be multiple rows > >>> with the same value for the column I am sorting on. Eg if sorting on a > >>> surname then there may be 100s of people with the same surname so > >>> generating a where clause that selects up to the exact person > >>> previously selected is very difficult. > >> > >>Then you should sort on surname AND whatever you are using as the prima= ry > >>key. > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.or > > > >g