Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Creating an A to Z list from query
February 9, 2006, 2:56 pm
rate this thread
I am creating an a to z list - basically a count of all results that
start with the letter "A", "B", "C" .... and so on.
I am pretty poor at SQL so I am sure some brains out there can do
better than I have here. What I have is working, I just want to make
sure that it is optomized.
So let's assume I have some query "$query" that I want to run and get
an A..Z list based on column "$column".
Let's further assume that '$query" produces the following results, and
that $column is equal to "last_name".
My AZlist query would look like this:
select * from
(SELECT count(alist.$column) as a from ($query) as alist where
alist.$column like 'a%' or alist.$column like 'A%' ) as a_result,
(SELECT count(blist.$column) as b from ($query) as blist where
blist.$column like 'b%' or blist.$column like 'B%' ) as b_result,
(SELECT count(zlist.$column) as z from ($query) as zlist where
zlist.$column like 'z%' or zlist.$column like 'Z%' ) as z_result;
And this retuns the following result:
a | b |...| z
1 | 3 |...| 0
Meaning that $query has 1 result where the first letter in $column is
"A" or "a", 3 results where the first letter is "B" or "b" and 0
results where the first letter is "Z" or "z".
What I am afraid of here is that "$query" is being executed 26 times
(once for each letter of the alphabet) . Is there a way to refine
this, or is MySQL (4.x and 5.x) smart enough to optomize this on its
- » I want to make transfer data between MySQL Server to MySQL Local .
- — Previous thread in » MySQL Database Forum
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum