On 28 Jan 2013, at 2:33am, Rick Guizawa <guizaw...@gmail.com> wrote:
> whereas column 'combo' contains all possible combinations of components in > table1 written in ascending order of table1 'bin' values. I can't think of how to say "pick one of the possible values in the bin" in SQL. You can do stuff like this: sqlite> CREATE TABLE myTable (component REAL,bin INTEGER,prd INTEGER); sqlite> INSERT INTO myTable VALUES (2.1,1,217); sqlite> INSERT INTO myTable VALUES (6.5,4,217); sqlite> INSERT INTO myTable VALUES (7.1,3,217); sqlite> INSERT INTO myTable VALUES (7.6,5,217); sqlite> INSERT INTO myTable VALUES (7.7,5,217); sqlite> INSERT INTO myTable VALUES (1.3,2,217); sqlite> INSERT INTO myTable VALUES (1.1,1,298); sqlite> INSERT INTO myTable VALUES (3.1,1,298); sqlite> INSERT INTO myTable VALUES (6.2,2,298); sqlite> INSERT INTO myTable VALUES (7.3,5,298); sqlite> INSERT INTO myTable VALUES (8.1,3,298); sqlite> INSERT INTO myTable VALUES (8.4,4,298); sqlite> INSERT INTO myTable VALUES (1.1,5,298); sqlite> SELECT * FROM myTable ORDER BY prd,bin,component; 2.1|1|217 1.3|2|217 7.1|3|217 6.5|4|217 7.6|5|217 7.7|5|217 1.1|1|298 3.1|1|298 6.2|2|298 8.1|3|298 8.4|4|298 1.1|5|298 7.3|5|298 sqlite> SELECT prd,group_concat(component,"|") FROM (SELECT * FROM myTable ORDER BY prd,bin,component) GROUP BY prd; 217|2.1|1.3|7.1|6.5|7.6|7.7 298|1.1|3.1|6.2|8.1|8.4|1.1|7.3 but I can't think of a way, within SQL, to say "pick one of these alternatives". My reaction would be to do it in your programming language. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
