SQL returns ORDER BY result as an array

Question

Is it possible to return groups as an associative array? I would like to know if a pure SQL solution is possible. Please note that I am releasing that I could make 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 need to be sorted alphabetically and grouped into separate groups according to the first letter of the word.

For instance:

ape
broom
coconut
banana
apple

must be returned as

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

(.. "A" , ", B" b .. JavaScript, .. ( AJAX).

: PostgreSQL, MySQL , PostgreSQL. - PHP.

Answer 1

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);

MySQL GROUP_CONCAT() PostgreSQL . http://mssql-to-postgresql.blogspot.com/2007/12/cool-groupconcat.html.

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

Answer 2

26 . .

Answer 3

LIKE?

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

a . .

Answer 4

PostgreSQL ( ), , - :

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

ANSI SQL. , MySql. Oracle SQL Server , Google, PostgreSQL 8.4 .