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);
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