On 1/27/2013 9:33 PM, Rick Guizawa wrote:
Hi, All. Could you, please, help me with sql query ?
For example, if I have a table1 like the following
(fields/values separated by commas):
component, "bin", "prd"
2.1 , "1", "217"
6.5 , "4", "217"
7.1 ,"3", "217"
7.6 ,"5", "217"
7.7 ,"5", "217"
1.3 ,"2", "217"
1.1 ,"1", "298"
3.1 ,"1", "298"
6.2 ,"2", "298"
7.3 ,"5", "298"
8.1 ,"3", "298"
8.4 ,"4", "298"
1.1 ,"5", "298"
and I want to produce a table2 below:
prd , combo
217 , 2.1|1.3|7.1|6.5|7.6
217 , 2.1|1.3|7.1|6.5|7.7
298 , 1.1|6.2|8.1|8.4|1.1
298 , 1.1|6.2|8.1|8.4|7.3
298 , 3.1|6.2|8.1|8.4|1.1
298 , 3.1|6.2|8.1|8.4|7.3
whereas column 'combo' contains all possible combinations of components in
table1 written in ascending order of table1 'bin' values.
This is impossible with a single SQL query. A query cannot possibly
produce more than O(N^m) rows, where N is the number of rows in all the
tables participating in the query, and m is the number of tables
mentioned in the FROM clause. In other words, the number of rows in the
resultset grows at most polynomially with the size of the database and
the length of the query.
But your hypothetical resultset may potentially contain O(2^N) rows -
that is, it may have to grow exponentially with the size of the
database. Therefore, it cannot be produced by a SQL query. QED.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
