pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dani Castaños <dcastanos@androme.es>
To: pgsql-php@postgresql.org
Subject: Re: Alphabetic Pager Class
Date: Thu, 21 Jun 2007 15:07:46 +0200
Message-ID: <467A7822.7020002@androme.es> (raw)
In-Reply-To: <3c1395330706210401l14c4656fv2af5787a34d5c80f@mail.gmail.com>
References: <467A39A8.6060008@androme.es>
	<3c1395330706210401l14c4656fv2af5787a34d5c80f@mail.gmail.com>



chris smith escribió:
> On 6/21/07, Dani Castaños <dcastanos@androme.es> 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);



view thread (4+ messages)

Message-ID: <467A7822.7020002@androme.es>
Permalink:  ../467A7822.7020002@androme.es/
Also on:    postgresql.org/message-id/467A7822.7020002@androme.es

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: dcastanos@androme.es
  Subject: Re: Alphabetic Pager Class
  In-Reply-To: <467A7822.7020002@androme.es>
  References: <467A39A8.6060008@androme.es> <3c1395330706210401l14c4656fv2af5787a34d5c80f@mail.gmail.com> <467A7822.7020002@androme.es>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox