Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 3F7B19FB7F7 for ; Thu, 21 Jun 2007 10:07:54 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 64096-08 for ; Thu, 21 Jun 2007 10:07:43 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from fafnir.androme.com (fafnir.androme.com [62.58.96.158]) by postgresql.org (Postfix) with ESMTP id CA7F99FB7FF for ; Thu, 21 Jun 2007 10:07:48 -0300 (ADT) Received: by fafnir.androme.com (Postfix, from userid 1003) id 6ABC5B94A5E; Thu, 21 Jun 2007 15:07:47 +0200 (CEST) Message-ID: <467A7822.7020002@androme.es> Date: Thu, 21 Jun 2007 15:07:46 +0200 From: =?ISO-8859-1?Q?Dani_Casta=F1os?= User-Agent: Thunderbird 2.0.0.4 (Windows/20070604) MIME-Version: 1.0 To: pgsql-php@postgresql.org Subject: Re: Alphabetic Pager Class References: <467A39A8.6060008@androme.es> <3c1395330706210401l14c4656fv2af5787a34d5c80f@mail.gmail.com> In-Reply-To: <3c1395330706210401l14c4656fv2af5787a34d5c80f@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200706/13 X-Sequence-Number: 3815 chris smith escribió: > On 6/21/07, Dani Castaños 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)); > Thanks chris! This is just what i needed! I got another solution, but I've used EXPLAIN ANALYZE, and yours is better: EXPLAIN ANALYZE select chr(x), count(1) from generate_series(32,90) x, sip_customer_services where upper(substring (sip_customer_services.service_name from 1 for 1)) = chr(x) group by 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ HashAggregate (cost=37.00..39.25 rows=150 width=4) (actual time=1.652..1.716 rows=13 loops=1) -> Hash Join (cost=9.38..36.25 rows=150 width=4) (actual time=0.979..1.490 rows=18 loops=1) Hash Cond: (chr("outer".x) = upper("substring"(("inner".service_name)::text, 1, 1))) -> Function Scan on generate_series x (cost=0.00..12.50 rows=1000 width=4) (actual time=0.114..0.332 rows=59 loops=1) -> Hash (cost=9.30..9.30 rows=30 width=10) (actual time=0.647..0.647 rows=18 loops=1) -> Seq Scan on sip_customer_services (cost=0.00..9.30 rows=30 width=10) (actual time=0.295..0.442 rows=18 loops=1) Total runtime: 2.147 ms (7 rows) test=# EXPLAIN ANALYZE select count(*), upper(substr(service_name, 1, 1)) from sip_customer_services group by upper(substr(service_name, 1,1)); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ HashAggregate (cost=9.60..10.13 rows=30 width=10) (actual time=0.704..0.766 rows=13 loops=1) -> Seq Scan on sip_customer_services (cost=0.00..9.45 rows=30 width=10) (actual time=0.332..0.530 rows=18 loops=1) Total runtime: 1.065 ms (3 rows);