X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 53D1E9F946D for ; Wed, 3 May 2006 09:45:57 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 12376-03 for ; Wed, 3 May 2006 09:45:47 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nz-out-0102.google.com (nz-out-0102.google.com [64.233.162.206]) by postgresql.org (Postfix) with ESMTP id 1FD269F9586 for ; Wed, 3 May 2006 09:45:46 -0300 (ADT) Received: by nz-out-0102.google.com with SMTP id 8so161897nzo for ; Wed, 03 May 2006 05:45:45 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=BoWZAsZQ5rP1/7GHGyImUkghSrnlMTgmy+cR1W9T0NCphMysW8dXpo58Xow6f3qvovJUkOiK1dMRPjKihcL7ykLLGncCd60EdlQ3VpRtMJS2RM1OD7Pgs3NXR7zO8GgL6Dv7dwOREQo2XgTizTMBMTGECWx8aHmq94k4TTIitGo= Received: by 10.36.49.6 with SMTP id w6mr2400955nzw; Wed, 03 May 2006 05:45:45 -0700 (PDT) Received: by 10.36.97.2 with HTTP; Wed, 3 May 2006 05:45:45 -0700 (PDT) Message-ID: <3c1395330605030545r3ff1c607uf2672f963aec6257@mail.gmail.com> Date: Wed, 3 May 2006 22:45:45 +1000 From: "chris smith" To: "Andy Dunlop" Subject: Re: Select and order by question Cc: pgsql-php@postgresql.org In-Reply-To: <1146647591.7286.284.camel@localhost.localdomain> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <1146647591.7286.284.camel@localhost.localdomain> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/5 X-Sequence-Number: 3505 On 5/3/06, Andy Dunlop wrote: > > Hi - I have the following code: > > //show table of current events needing action > $sSql =3D "SELECT id FROM crm_leads "; > $sLeads =3D pg_exec($conn,$sSql); > for($e=3D0;$e $sSql =3D "SELECT * FROM crm_events WHERE crm_leads_id =3D ".pg_= result($sLeads,$e,"id")." AND follow_up_action!=3D'' ORDER BY follow_up_dat= e asc limit 1"; > $sRes =3D pg_exec($conn,$sSql); > $sHits =3D pg_numrows($sRes); > if($sHits!=3D0){ > for($i=3D0;$i<$sHits;$i++){ > // generate the display here > } > } > > crm_leads is a parent with many crm_events as it's children. > This code gives me the correct set of rows. > My problem is that I want only the last row from crm_events (as per limi= t 1) but I want those rows sorted by follow_up_date. I guess I need to have= the whole query in one select statement? But how? select * from crm_leads l, crm_events e where l.id=3De.crm_leads_id and follow_up_action !=3D '' ORDER BY follow_up_date ASC LIMIT 1; Make sure crm_events crm_leads_id has an index on id and cram_leads id.. -- Postgresql & php tutorials http://www.designmagick.com/