* Question*:

[Joe Celko’s One In Ten Puzzle]

Alan Flancman ran into a problem with some legacy system data that had been moved over to a SQL database. The table looked like this:

CREATE TABLE MyTable (keycol INTEGER NOT NULL, f1 INTEGER NOT NULL, f2 INTEGER NOT NULL, … f10 INTEGER NOT NULL, );The columns

`f1`through

`f10`were an attempt to flatten out an array into a table. What he wanted was an elegant way to test against the

`f1`through

`f10`columns to find the rows that had exactly one non-zero in their

`f`-columns.

How many different approaches can you find?

__ Answer__:

*Solution One:*

You could use the Sign() function in Sybase and other SQL products. This function returns -1, 0, or +1 if the argument is negative, zero, or positive respectively. Assuming that your numbers are zero or greater, you simply write:

SELECT * FROM MyTable WHERE Sign(f1) + Sign(f2) + … + Sign(f10) = 1;to find a single non-zero value. If you can have negative values, then make the functions

`Sign(ABS(fn))`.

The Sign() function can be written with the CASE expression in SQL-92 as:

CASE WHEN x > 0 THEN 1 WHEN x = 0 THEN 0 WHEN x < 0 THEN -1 OTHERWISE NULL END

*Solution Two:*

Since the fields are really an attempt to fake an array, you should normalize this table into 1NF, like so:

CREATE TABLE Foobar (keycol INTEGER NOT NULL, i INTEGER NOT NULL CHECK (i BETWEEN 1 AND 10), f INTEGER NOT NULL, PRIMARY KEY (keycol, i));The extra column

`i`is really the subscript for the array. You now view the problem as finding an entity which has exactly nine zero valued

`f`s, instead of as finding an entity which has exactly one non-zero valued

`f`. That is suddenly easy:

SELECT keycol FROM Foobar WHERE f = 0 GROUP BY keycol HAVING COUNT(*) = 9;You can create a VIEW that has the structure of Foobar, but things are going to run pretty slow unless you have a good optimizer:

CREATE VIEW Foobar (keycol, f) AS SELECT keycol, f1 FROM MyTable UNION SELECT keycol, f2 FROM MyTable UNION … UNION SELECT keycol, f10 FROM MyTable;

*Solution Three:*

This depends on a feature of SQL-92 that is not generally available yet.

SELECT * FROM MyTable WHERE (f1, f2, … , f10) IN ((f1, 0, 0, 0, 0, 0, 0, 0, 0, 0), (0, f2, 0, 0, 0, 0, 0, 0, 0, 0), …. (0, 0, 0, 0, 0, 0, 0, 0, 0, f10));In SQL-92, you can use row constructors in comparison predicates. The IN predicate expands into a sequence of OR-ed equality predicates. The row-wise version of equality is then done on a position by position basis, where all corresponding values must be equal.

*Puzzle provided courtesy of:Joe Celko[email protected]*