On 3 Jan 2012, at 5:15pm, guiz wrote:
> Hi all, if I have a table like
> CREATE TABLE [a] (
> [ID] INTEGER NOT NULL,
> [prdtype] VARCHAR(35) NOT NULL
> )
>
> and data already inserted as
>
> insert into a (ID, prdtype) values (1, '15');
> insert into a (ID, prdtype) values (2, '15|10|27|3');
> insert into a (ID, prdtype) values (3, '8|6|22');
>
> and I'd like to update table 'a' to achieve sorted result in prdtype as
>
> ID prdtype
> 1 15
> 2 3|10|15|27
> 3 6|8|22
No simple way to do it. If you want to be able to sort things inside SQL, you
have to hold the data in SQL tables rather than strings. Replace your schema
with
CREATE TABLE prds (
id INTEGER NOT NULL
type INTEGER NOT NULL)
insert into a (ID, prdtype) values (1, 15);
insert into a (ID, prdtype) values (2, 15);
insert into a (ID, prdtype) values (2, 10);
insert into a (ID, prdtype) values (2, 27);
insert into a (ID, prdtype) values (2, 3);
...
SELECT id,group_concat(type) FROM (SELECT ID, prdtype FROM prds ORDER BY id,
prdtype);
The reason you need a sub-SELECT is that normally the group_concat is
considered first, and the ORDER BY applied to the result.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
