Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 715109FC0E2 for ; Thu, 21 Jun 2007 08:01:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 97017-06 for ; Thu, 21 Jun 2007 08:01:07 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.238]) by postgresql.org (Postfix) with ESMTP id A5F459FC0D1 for ; Thu, 21 Jun 2007 08:01:07 -0300 (ADT) Received: by nz-out-0506.google.com with SMTP id m22so459773nzf for ; Thu, 21 Jun 2007 04:01:06 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=k5wesOcfNq64TrqT2I6YD9rb6OpnrtgeeRObIZ3K9EdZaYsfoMO+TwILtaHpU/DcHt6e/WmcwfrBqQiQenUasxeC4NE8aSI72X77nO2H5NZuNjtUS6peu2+RCrJTtXS679STvEqgIHxjn1m/l8Z495ZyT1OTnU2lM/INmcS7yfI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=sQfFZHHZ96HmJDizD/sGSW9zez5+JB63M8FWFJ3izdQD3wz6qhLGRNRbvlOicDdkvSvm8eUDkkjYQ6cDUVnaegiku6hdp4pMMmSe6bFq+gaDyX2/BdAngCtflfI5Rhwsc0J30/lPwfALZ7lO4rHgtZD72zmgG5JhBJwUvayr7+U= Received: by 10.114.153.18 with SMTP id a18mr1457198wae.1182423666239; Thu, 21 Jun 2007 04:01:06 -0700 (PDT) Received: by 10.114.158.19 with HTTP; Thu, 21 Jun 2007 04:01:06 -0700 (PDT) Message-ID: <3c1395330706210401l14c4656fv2af5787a34d5c80f@mail.gmail.com> Date: Thu, 21 Jun 2007 21:01:06 +1000 From: "chris smith" To: "=?ISO-8859-1?Q?Dani_Casta=F1os?=" Subject: Re: Alphabetic Pager Class Cc: pgsql-php@postgresql.org In-Reply-To: <467A39A8.6060008@androme.es> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <467A39A8.6060008@androme.es> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/12 X-Sequence-Number: 3814 On 6/21/07, Dani Casta=F1os wrote: > Hello! > > I'm trying to build an alphabetic pager class. For instance, an address > book ordered by the Last Name where only results begining with A are > shown, and A - B - C - D - ... links below to change the letter which > i'm filtering for. > The point is I don't want to have enabled those links wich have no > occurrences in them. So, I'm wondering which is the best way to do the > query. > > My first thought to know how many results are there for each, is to do > something like: > SELECT COUNT(field) FROM table WHERE UPPER( field ) LIKE UPPER( 'A%' ); > SELECT COUNT(field) FROM table WHERE UPPER( field ) LIKE UPPER( 'B%' ); > SELECT COUNT(field) FROM table WHERE UPPER( field ) LIKE UPPER( 'C%' ); select count(*), upper(substr(field, 1, 1)) from table group by upper(substr(field, 1, 1)); --=20 Postgresql & php tutorials http://www.designmagick.com/