I know sum() is work. This issue occurs only for TEXT. When I use a user-defined aggregate CONCAT() it led to a stange result. suppose the length of original text is 1 length of output text via GROUP+concat() is 5 while via ORDER, the length of final text is 1 (should be 5)
ps1. run on winxp, compile by gcc(mingw) ps2. may u try concat() written by me http://wakka.myweb.hinet.net/sqlite3ext.zip 2007/10/14, [EMAIL PROTECTED] <[EMAIL PROTECTED]>: > Wakka <[EMAIL PROTECTED]> wrote: > > 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? > > Works OK when I try it. I don't have your custom concat() > function, so I had to use sum() instead. Here is my test: > > CREATE TABLE t1(a); > INSERT INTO t1 VALUES(100); > INSERT INTO t1 VALUES(20); > INSERT INTO t1 VALUES(3); > CREATE TABLE t2(b); > INSERT INTO t2 VALUES('a'); > INSERT INTO t2 VALUES('b'); > INSERT INTO t2 VALUES('c'); > > SELECT b, sum(a) > FROM t2, t1 > GROUP BY b; > SELECT '----------------------'; > SELECT b, sum(a) > FROM t2, t1 > GROUP BY b > ORDER BY b; > > And I get the same output either way: > > a|123 > b|123 > c|123 > ---------------------- > a|123 > b|123 > c|123 > > > > > > ### 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] > ----------------------------------------------------------------------------- > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
