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 E54929FB261 for ; Wed, 3 May 2006 07:23:52 -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 51557-02-5 for ; Wed, 3 May 2006 07:23:39 -0300 (ADT) X-Greylist: delayed 00:31:21.09242 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id B11BD9FB391 for ; Wed, 3 May 2006 06:56:04 -0300 (ADT) Received: from ctb-mesg8.saix.net (ctb-mesg8.saix.net [196.25.240.78]) by svr4.postgresql.org (Postfix) with ESMTP id E449B5B1656 for ; Wed, 3 May 2006 09:14:37 +0000 (GMT) Received: from [111.112.113.9] (dsl-146-176-00.telkomadsl.co.za [165.146.176.0]) by ctb-mesg8.saix.net (Postfix) with ESMTP id 6F5363125 for ; Wed, 3 May 2006 11:14:27 +0200 (SAST) Subject: Select and order by question From: Andy Dunlop To: pgsql-php@postgresql.org Content-Type: multipart/related; type="multipart/alternative"; boundary="=-sOG5NXamD3uWs9jff1xs" Organization: Infocus Date: Wed, 03 May 2006 11:13:10 +0200 Message-Id: <1146647591.7286.284.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.273 tagged_above=0 required=5 tests=BAYES_05, EXTRA_MPART_TYPE, HTML_50_60, HTML_IMAGE_ONLY_20, HTML_MESSAGE X-Spam-Level: * X-Archive-Number: 200605/3 X-Sequence-Number: 3503 --=-sOG5NXamD3uWs9jff1xs Content-Type: multipart/alternative; boundary="=-Zh7ZMpfLl0g840bQSXCt" --=-Zh7ZMpfLl0g840bQSXCt Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi - I have the following code: //show table of current events needing action $sSql = "SELECT id FROM crm_leads "; $sLeads = pg_exec($conn,$sSql); for($e=0;$e Hi - I have the following code:

//show table of current events needing action
$sSql = "SELECT id FROM crm_leads ";
$sLeads = pg_exec($conn,$sSql);
for($e=0;$e<pg_numrows($sLeads);$e++){
        $sSql = "SELECT * FROM crm_events WHERE crm_leads_id = ".pg_result($sLeads,$e,"id")." AND follow_up_action!='' ORDER BY follow_up_date asc limit 1";
        $sRes = pg_exec($conn,$sSql);
        $sHits = pg_numrows($sRes);
        if($sHits!=0){
                for($i=0;$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 limit 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?

Any help appreciated
Thanks
Andy Dunlop

www.infocus.co.za
+27 21 532 0335 office
+27 82 770 8749 mobile
--=-Zh7ZMpfLl0g840bQSXCt-- --=-sOG5NXamD3uWs9jff1xs Content-ID: <1146646853.7286.270.camel@localhost.localdomain> Content-Disposition: attachment; filename=infocus_cropped.jpg Content-Type: image/jpeg; name=infocus_cropped.jpg Content-Transfer-Encoding: base64 /9j/4AAQSkZJRgABAQEAlQCVAAD/4QAWRXhpZgAATU0AKgAAAAgAAAAAAAD//gAXQ3JlYXRlZCB3 aXRoIFRoZSBHSU1Q/9sAQwAFAwQEBAMFBAQEBQUFBgcMCAcHBwcPCwsJDBEPEhIRDxERExYcFxMU GhURERghGBodHR8fHxMXIiQiHiQcHh8e/9sAQwEFBQUHBgcOCAgOHhQRFB4eHh4eHh4eHh4eHh4e Hh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4e/8AAEQgAJABzAwEiAAIRAQMRAf/E AB0AAAIBBQEBAAAAAAAAAAAAAAAIBwEDBAUGAgn/xAA4EAABAwQBAwEGAgcJAAAAAAABAgMEAAUG EQcSITFBCBMiUWFxFGIyRFOBkaHBFRYkJTlSZYLw/8QAGgEBAAMBAQEAAAAAAAAAAAAAAAECBAYD B//EACARAAMAAQUBAAMAAAAAAAAAAAABAgMEBREhMVESMkL/2gAMAwEAAhEDEQA/AGZumfR4HJVv whVlubjs1oOJmoaJjt9lnSleAfg158qFdeV638J+nY1AGZck5dbfbCxfjuHcUIx64QQ9JjmO2VLU W3z2X09Q7to8H0+tYntbcoZzx/mGDW7DZTCBeHHUyI7kdCxIKVspSjqUCUg9ZGx370AxYWCe3f7V j3KdGt8CROmOJZjxmlPOrV4ShIKlH9wBNQJl2Jc6qsEvJ2uTWodwjx1SU25iIkMICQVe78Hfy2dm uRh5ryRyv7ON8ukK/W6zybZDmtXttUJK/wAc0lhfUlPb4CRvuPFCV6d0faiwV6WtEK3XqXDQ97lU 1uMr3QVvQ76+lWLz7QcF6SpqxxA+2lXT7xXrSbWa5ZOvjWJakXkJsKJDr34NDQSpKuxKlKA2QT6b rLwhjIL6ytUOf+AhtnXUEgk/M1ky5qX6s7LYNrwcqtXidO1zKXz6O3iPL6Z7iW7hFDYPlST2FSxb p8edGRIjrC0KGwRXz3evWVcfTIUq7y03azSHAhThR8bf2NTjmnL8rjnC467NGbn3G6LDcBogqSPz Eev0FWxZK/o8d80Oin8q08uKn2WNAVjW+1AWCkEDtS+xMB5+uNsF1uXKyIN3U31iHHiIDLatbCDo d/lWy4/y/ldHHeUKz6ymBeLMw6Ytx9wEtyukHSgnWvT5VpOSJvS51dwN/Y/+FV69b7eKhz2RM5yX kLiz+3cqmtzJ4mONBxLCGvhB7dkgCtLy3yNlmPe09gGE2q4IZsV5YaXNYMZC1LJddSdLKSodkjwR QE+hffRBqvV89D50o03k7Ms55jyHDm86g8f2y0vFmOhbaPey1BSk7Ss+fG+3oRXe4ZH5wx7kGFCf v8TNMTfSFPS1BtDjG9jyN79P40BPmz/tNFedKV3CtD6GigFR5F/1DMIP/GpGu37KVR7a3fmfhrtv /M+/r+sRqZqRjOPSMgZyF+x2x28MJ6WZ64iFPtjuNBzXUBpR8H1ql6xfHb3LiTLzYrZcZUJXVEel xEPLjnYO0FQJSdgHtQBl3fFLvs/qL50d9/gVSqezGlQ9l/lLsSPw9wA+R/w7lN+4y240tlxCFtLB SpBTsKBHcEeu61NvxTGbZbJVttuP2mFClhSZMdiG2228FDSutIGlbBO90QPnhx/Yn5vF7LnSoJkK kNNkg6347fyqnG90t1nhyccyVZgrStYC17SlQV6g/up6sm4zx2Vj6bXZbRBtLbKittqFHQy2CfPw oAGz89VE9z4wAdDd2skeakHQLzCXBr7EarBkhy2n4z6Lt26Ys2mwuL4y405afjRAHItxiZhHtuFY cg3F9T6et1s9TaQPHf8ArXf854hemcdxq+WWG7cf7tutLkNJSVK6U9woJHkef4VMmJYQ3AQGrRZY 8EK7H3LIbH8BUrYvYm7XF0vSnXP0yfkfT7VOJunxK6Rz+8apW6qqVVXw4Cye0PxVNx5FzfyiLBe9 0FLhSF9L6Fa7p6T3J9K53CeR8j5fx3NGYmNiHj7cZ1i2TVJWFyuxA7E9zrRqV5nHWBTJv42VhWNv yurr985a2VL38+op3XRR4rEZltiO0hlpsAIQ2npSkAa0B6Dx2FbjmhSfY15Mw/B+P52JZjd2LBdI k9xZROV7sLSddwT9dj91afO80t+ce2Tx1crQy+u3sFtiK+tHSmQA47tafyhRI3+XdNldsFwu7zvx 91xKwz5f7eRbmXHD/wBindZEjE8ZkXOFc5GP2l6dAQEQpLkNtTsYDZ02ojaRsnxryaAWfLsm4ezP kW+WDl3C28Ynw3Pds3B5ZbU+ATpSnW9b2AkjZPpUfX9mwYXm2PxeAs7u94kyZmpFvjSVPx0oJGyr XbxvzTuX7GMdyBKU36xWu7JQOlImw23gkfIdYNW7DiWMY+oqsOPWm1KIIJhw22d9/wAoFAbKEHVQ 2VSh0yC2kuhPgK13/nRWQQfQgD7UUBWiiigCgjYooqGC2P60KQhf6SQr7iiirUuist9HhISlXSlI A+gq8AN+KKKrfSXB6MrRRRUlQooooAooooAooooD/9k= --=-sOG5NXamD3uWs9jff1xs--