Re: [sqlite] How to sort within a text field

2012-01-05 Thread Black, Michael (IS)
Along with the sortprds code and database I sent out previously this now works. gcc -g -o main main.c -DSQLITE_ENABLE_LOAD_EXTENSION sqlite3.c -lpthread -ldl ./main test.db "select id,sortprds(prdtype) from a order by sortprds(prdtype)" ID = 2 sortprds(prdtype) = 3|10|15|27 ID =

Re: [sqlite] How to sort within a text field

2012-01-04 Thread Kit
2012/1/3 guiz : > 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, prdty

Re: [sqlite] How to sort within a text field

2012-01-04 Thread Black, Michael (IS)
Your question made me want to do something I've been meaning to for a while...a custom function. So...the following words as loadable extension (at least for the shell) gcc -g -shared -fPIC -o sortprds.sqlext sortprds.c sqlite3 test.db SQLite version 3.7.9 2011-11-01 00:52:41 Enter ".help

Re: [sqlite] How to sort within a text field

2012-01-03 Thread jr
On 03/01/12 17:15, 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

Re: [sqlite] How to sort within a text field

2012-01-03 Thread guiz
Simon and Kit-18, thank you, I guess I'll restructure my schema. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://old.nabble.com/How-to-sort-

Re: [sqlite] How to sort within a text field

2012-01-03 Thread Simon Slavin
On 3 Jan 2012, at 5:50pm, Kit wrote: > 2012/1/3 Simon Slavin : >> SELECT id,group_concat(type) FROM (SELECT ID, prdtype FROM prds ORDER BY id, >> prdtype); >> Simon. > > SELECT id,group_concat(prdtype) FROM (SELECT ID, prdtype FROM prds > ORDER BY id, prdtype) GROUP BY id; Thanks. I forgot th

Re: [sqlite] How to sort within a text field

2012-01-03 Thread Kit
2012/1/3 Simon Slavin : > SELECT id,group_concat(type) FROM (SELECT ID, prdtype FROM prds ORDER BY id, > prdtype); > Simon. SELECT id,group_concat(prdtype) FROM (SELECT ID, prdtype FROM prds ORDER BY id, prdtype) GROUP BY id; -- Kit ___ sqlite-users ma

Re: [sqlite] How to sort within a text field

2012-01-03 Thread Simon Slavin
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');

[sqlite] How to sort within a text field

2012-01-03 Thread guiz
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