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]
-----------------------------------------------------------------------------
