Обсуждение: Alphabetic Pager Class

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%' );
...
and so on. But obviously, it is not an effcient way to do this.
Is it possible to reduce this to only one query??

Thank you in advance.

Dani

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

--
Postgresql & php tutorials
http://www.designmagick.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);