Re: Alphabetic Pager Class
От | Dani Castaños |
---|---|
Тема | Re: Alphabetic Pager Class |
Дата | |
Msg-id | 467A7822.7020002@androme.es обсуждение исходный текст |
Ответ на | Re: Alphabetic Pager Class ("chris smith" <dmagick@gmail.com>) |
Список | 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);
В списке pgsql-php по дате отправления: