SQL return ORDER BY result as an array

Is it possible to return groups as an associative array? I'd like to know if a pure SQL solution is possible. Note that I release that I could be making things more complex unnecessarily but this is mainly to give me an idea of the power of SQL.

My problem: I have a list of words in the database that should be sorted alphabetically and grouped into separate groups according to the first letter of the word.

For example:

ape
broom
coconut
banana
apple

should be returned as

array(
'a' => 'ape', 'apple',
'b' => 'banana', 'broom',
'c' => 'coconut'
)

so I can easily created sorted lists by first letter (i.e. clicking "A" only shows words starting with a, "B" with b, etc. This should mak开发者_如何学Pythone it easier for me to load everything in one query and make the sorted list JavaScript based, i.e. without having to reload the page (or use AJAX).

Side notes: I'm using PostgreSQL but a solution for MySQL would be fine too so I can try to port it to PostgreSQL. Scripting language is PHP.

MySQL:

SELECT LEFT(word, 1) AS first_letter, 
  GROUP_CONCAT(word) AS word_list
FROM MyTable
GROUP BY LEFT(word, 1);

PostgreSQL 8.4:

SELECT SUBSTRING(word FOR 1) AS first_letter, 
  ARRAY_TO_STRING(ARRAY_AGG(word), ',') AS word_list
FROM MyTable
GROUP BY SUBSTRING(word FOR 1);

See http://mssql-to-postgresql.blogspot.com/2007/12/cool-groupconcat.html for more about emulating MySQL's GROUP_CONCAT() in PostgreSQL.

See http://www.postgresonline.com/journal/index.php?/archives/126-PostgreSQL-8.4-Faster-array-building-with-array_agg.html for more on ARRAY_AGG().

Run 26 separate queries. Or run one query and separate the results alphabetically server side.

Are you familiar with the LIKE syntax?

as in

SELECT * FROM words WHERE col LIKE a% ORDER BY col

would give you the a's in order and so on. Build the hash accordingly.

I'm no PostgreSQL expert (or user) but I believe in the later versions you can do something like this:

SELECT 
    ROW_NUMBER() OVER (PARTITION BY SUBSTRING(whatever from 1 for 1) ORDER BY whatever) AS __ROW,
    whatever
FROM yourtable;

This is ANSI SQL. I don't know what the support on MySql is like yet. Oracle and SQL Server both work with this syntax and I heard it through Google that PostgreSQL 8.4 supports windowing functions.