That depends on what you mean by "Could this be achieved in SQLite?".
There is no query (in any SQL engine) that can depend on a sub-query
that is itself dependent on the outcome of the main query. This is what
makes recursion beautiful, but then there is also no CTE (or other query
in any SQL engine) that can recurse over multiple states of data (i.e.
query data in one single query to reflect results from both before and
after a delete in the source table), nor can a CTE be updated or deleted
from, its data must persist atomically (with some exceptions when using
non-deterministic functions, like random).
These are not so much "inabilities" of SQL engines, but more due to
explicit SQL and set-algebra rules.
So this is not possible in a single query.
You can of course "achieve" it using any SQL engine by constructing a
temporary table, and then repeatedly run a DELETE query for all x values
where COUNT(y) is less than nY, then DELETE all y values where COUNT(x)
< nX, rinse, repeat until both SELECT y HAVING COUNT(x) < nX and SELECT
x HAVING COUNT(y) < nY aggregate queries return empty sets - but this
would be painfully slow next to a simple software algorithm that
prunes/resolves a 2-dimensional array - exponentially worse so for
larger grid sizes.
On 2018/05/01 2:45 AM, Roman Fleysher wrote:
Dear SQLiters,
I have trouble solving this problem, maybe it is impossible?
I have a table with two columns x and y, both integers. Imagine they are
coordinates on X-Y plane, dots. I need to find all x's that have more than nX
dots, and all y's that have more than nY dots. Both conditions must be
simultaneous in the following sense:
If x=10 has less than nX dots, all dots with x=10 are deleted. Because of
deletion, y=3 which previously had more than nY dots no longer passes the
threshold and thus y=3 must be deleted too. This could cause deletion of some
other x, etc. At the end, number of dots on all vertical lines must be more
than nX and number of dots on all horizontal lines must be more than nY.
Could this be achieved with SQLite?
Roman
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
