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


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Alphabetic Pager Class

------=_Part_2881_14495121.1182422880068
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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=F1os 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
>

------=_Part_2881_14495121.1182422880068
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Re: Alphabetic Pager Class

On 6/21/07, Dani Casta=F1os 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));

--=20
Postgresql & php tutorials
http://www.designmagick.com/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Alphabetic Pager Class

chris smith escribi=F3:
> On 6/21/07, Dani Casta=F1os wrote:
>> Hello!
>>
>> I'm trying to build an alphabetic pager class. For instance, an addres=
s
>> 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 bette=
r:

EXPLAIN ANALYZE select chr(x), count(1) from generate_series(32,90) x,=20
sip_customer_services where upper(substring=20
(sip_customer_services.service_name from 1 for 1)) =3D chr(x) group by 1;
QUERY PLAN
------------------------------------------------------------ -------------=
-----------------------------------------------------------
HashAggregate (cost=3D37.00..39.25 rows=3D150 width=3D4) (actual=20
time=3D1.652..1.716 rows=3D13 loops=3D1)
-> Hash Join (cost=3D9.38..36.25 rows=3D150 width=3D4) (actual=20
time=3D0.979..1.490 rows=3D18 loops=3D1)
Hash Cond: (chr("outer".x) =
upper("substring"(("inner".service_name)::text, 1, 1)))
-> Function Scan on generate_series x (cost=3D0.00..12.50=20
rows=3D1000 width=3D4) (actual time=3D0.114..0.332 rows=3D59 loops=3D1)
-> Hash (cost=3D9.30..9.30 rows=3D30 width=3D10) (actual=20
time=3D0.647..0.647 rows=3D18 loops=3D1)
-> Seq Scan on sip_customer_services (cost=3D0.00..9.30=20
rows=3D30 width=3D10) (actual time=3D0.295..0.442 rows=3D18 loops=3D1)
Total runtime: 2.147 ms
(7 rows)

test=3D# EXPLAIN ANALYZE select count(*), upper(substr(service_name, 1,=20
1)) from sip_customer_services group by upper(substr(service_name, 1,1));
QUERY PLAN
------------------------------------------------------------ -------------=
-----------------------------------------------
HashAggregate (cost=3D9.60..10.13 rows=3D30 width=3D10) (actual=20
time=3D0.704..0.766 rows=3D13 loops=3D1)
-> Seq Scan on sip_customer_services (cost=3D0.00..9.45 rows=3D30=20
width=3D10) (actual time=3D0.332..0.530 rows=3D18 loops=3D1)
Total runtime: 1.065 ms
(3 rows);

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org