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/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 <dcastanos [at] 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
>

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

<span class=3D"gmail_quote"><br><br></span>
<div>Hi Dani!!</div>
<div> </div>
<div>like</div>
<div><br>SELECT a.COUNT(field), b.COUNT(field),.... FROM table a, table b, =
table c, .....</div>
<div>WHERE  UPPER( a.field )  LIKE UPPER( 'A%' ) AND=
</div>
<div>UPPER( b.field )  LIKE UPPER( 'B%' )  AND</div>
<div>.</div>
<div>.</div>
<div>.</div>
<div>.</div>
<div> </div>
<div>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!!</div>
<div> </div>
<div> </div>
<div> </div>
<div> </div>
<div>What do u think?</div>
<div>(Q t parece?)</div>
<div> </div>
<div>Regards (Saludos)</div>
<div>Rafa</div>
<div>La Victoria, Venezuela<br><br><br> </div>
<div><span class=3D"e" id=3D"q_1134de364c373b82_1">
<div><span class=3D"gmail_quote">On 6/21/07, <b class=3D"gmail_sendername">=
Dani Casta=F1os</b> <<a onclick=3D"return top.js.OpenExtLink(window,even=
t,this)" href=3D"mailto:dcastanos [at] androme.es" target=3D"_blank">dcastanos [at] a=
ndrome.es
</a>> wrote:</span>
<blockquote class=3D"gmail_quote" style=3D"PADDING-LEFT: 1ex; MARGIN: 0px 0=
px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">Hello!<br><br>I'm trying to =
build an alphabetic pager class. For instance, an address<br>book ordered b=
y the Last Name where only results begining with A are
<br>shown, and A - B - C - D - ... links below to change the letter which<b=
r>i'm filtering for.<br>The point is I don't want to have enabled t=
hose links wich have no<br>occurrences in them. So, I'm wondering which=
is the best way to do the
<br>query.<br><br>My first thought to know how many results are there for e=
ach, is to do<br>something like:<br>SELECT COUNT(field) FROM table WHERE UP=
PER( field )  LIKE UPPER( 'A%' );<br>SELECT COUNT(field) =
FROM table WHERE UPPER( field )  LIKE UPPER( 'B%' );
<br>SELECT COUNT(field) FROM table WHERE UPPER( field )  LIKE UPP=
ER( 'C%' );<br>...<br>and so on. But obviously, it is not an effcie=
nt way to do this.<br>Is it possible to reduce this to only one query??<br>=
<br>Thank you in advance.
<br><br>Dani<br><br><br>---------------------------(end of broadcast)------=
---------------------<br>TIP 7: You can help support the PostgreSQL project=
by donating at<br><br>        &nbs=
p;      <a onclick=3D"return top.js.OpenExtLink(wi=
ndow,event,this)" href=3D"http://www.postgresql.org/about/donate" target=3D=
"_blank">
http://www.postgresql.org/about/donate</a><br></blockquote></div><br></span=
></div>

------=_Part_2881_14495121.1182422880068--

On 6/21/07, Dani Casta=F1os <dcastanos [at] 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/

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

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

chris smith escribi=F3:
> On 6/21/07, Dani Casta=F1os <dcastanos [at] androme.es> 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,
sip_customer_services where upper(substring
(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
time=3D1.652..1.716 rows=3D13 loops=3D1)
-> Hash Join (cost=3D9.38..36.25 rows=3D150 width=3D4) (actual
time=3D0.979..1.490 rows=3D18 loops=3D1)
Hash Cond: (chr("outer".x) =3D
upper("substring"(("inner".service_name)::text, 1, 1)))
-> Function Scan on generate_series x (cost=3D0.00..12.50
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
time=3D0.647..0.647 rows=3D18 loops=3D1)
-> Seq Scan on sip_customer_services (cost=3D0.00..9.30
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,
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
time=3D0.704..0.766 rows=3D13 loops=3D1)
-> Seq Scan on sip_customer_services (cost=3D0.00..9.45 rows=3D30
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