I get into trouble about using GROUP and ORDER. When I use GROUP and ORDER together, aggregate function can't work, could someone explain it?
### create two table CREATE TABLE book (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE); INSERT INTO book VALUES (0); INSERT INTO book VALUES (0); INSERT INTO book VALUES (0); CREATE TABLE author (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE); INSERT INTO author VALUES (0); INSERT INTO author VALUES (0); INSERT INTO author VALUES (0); book author id id ---- ------ 1 1 2 2 3 3 ### query 1 SELECT book.id concat(author.id, ',') FROM book, author GROUP BY book.id; book.id author.id -------------------- 1 1,2,3 2 1,2,3 3 1,2,3 ### query 2 SELECT book.id concat(author.id, ',') FROM book, author GROUP BY book.id ORDER BY book.id; book.id author.id -------------------- 1 1 2 1 3 1 The result of query 2 is strange. Here is the my expected result. book.id author.id -------------------- 1 1,2,3 2 1,2,3 3 1,2,3 I had try text as source data too, the result is the same. it appears that ORDER use the original length of the data. How to resolve/workaround this issue?? ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
