pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Alphabetic Pager Class
4+ messages / 3 participants
[nested] [flat]

* Alphabetic Pager Class
@ 2007-06-21 08:41  Dani Castaños <dcastanos@androme.es>
  0 siblings, 2 replies; 4+ messages in thread

From: Dani Castaños @ 2007-06-21 08:41 UTC (permalink / raw)
  To: pgsql-php

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Alphabetic Pager Class
@ 2007-06-21 10:48  Rafael Mora <moraloco@gmail.com>
  parent: Dani Castaños <dcastanos@androme.es>
  1 sibling, 0 replies; 4+ messages in thread

From: Rafael Mora @ 2007-06-21 10:48 UTC (permalink / raw)
  To: pgsql-php

Hi Dani!!

like

SELECT a.COUNT(field), b.COUNT(field),.... FROM table a, table b, table c,
.....
WHERE  UPPER( a.field )  LIKE UPPER( 'A%' ) AND
UPPER( b.field )  LIKE UPPER( 'B%' )  AND
.
.
.
.

I think you should change the AND for OR, 'cuz there you can get something
anyways even if you dont have a letter on the table!!




What do u think?
(Q t parece?)

Regards (Saludos)
Rafa
La Victoria, Venezuela



 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%' );
> ...
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                http://www.postgresql.org/about/donate
>

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Alphabetic Pager Class
@ 2007-06-21 11:01  chris smith <dmagick@gmail.com>
  parent: Dani Castaños <dcastanos@androme.es>
  1 sibling, 1 reply; 4+ messages in thread

From: chris smith @ 2007-06-21 11:01 UTC (permalink / raw)
  To: Dani Castaños <dcastanos@androme.es>; +Cc: pgsql-php

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/



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Alphabetic Pager Class
@ 2007-06-21 13:07  Dani Castaños <dcastanos@androme.es>
  parent: chris smith <dmagick@gmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Dani Castaños @ 2007-06-21 13:07 UTC (permalink / raw)
  To: pgsql-php



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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2007-06-21 13:07 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-21 08:41 Alphabetic Pager Class Dani Castaños <dcastanos@androme.es>
2007-06-21 10:48 ` Rafael Mora <moraloco@gmail.com>
2007-06-21 11:01 ` chris smith <dmagick@gmail.com>
2007-06-21 13:07   ` Dani Castaños <dcastanos@androme.es>

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