I am using a SQL server database with around 20 columns,all
the columns have numeric values, I want to write an SQL statement
which does the following:
compare each row of the table with all other rows in the table and
return all the rows that have a difference of + or - 0.5 in each
column, for eg if row1 has values 12.2,13.6,11.4,15.7 corresponding
to column1,2,3,4 the sql statement should return all the rows from the
table with values of column 1-4 between
12.2- 0.5 to 12.2 + 0.5
13.6 -0.5 to 13.6+ 0.5
11.4 -0.5 to 11.4 +0.5
15.7 -0.5 to 15.7 +0.5
so effectively this statement would search for groups of rows that
have matching values(diffence of + or - 0.5)
Could anyone suggest how i go about doing this.
thank you in advance
harshaIt's not clear to me exactly what you want to see in the result. If you just
want to *join* like rows based on the criteria you've specified then you
could do so as follows (I've had to assume a primary key because you didn't
specify one).
CREATE TABLE SomeTable (keycol INTEGER PRIMARY KEY, c1 NUMERIC(3,1) NOT
NULL, c2 NUMERIC(3,1) NOT NULL, c3 NUMERIC(3,1) NOT NULL, c4 NUMERIC(3,1)
NOT NULL)
/* Sample data */
INSERT INTO SomeTable VALUES (1, 12.2, 13.6, 11.4, 15.7)
INSERT INTO SomeTable VALUES (2, 12.0, 13.9, 10.9, 15.2)
SELECT S1.keycol, S1.c1, S1.c2, S1.c3, S1.c4,
S2.keycol, S2.c1, S2.c2, S2.c3, S2.c4
FROM SomeTable AS S1
JOIN SomeTable AS S2
ON S1.keycol < S2.keycol
AND ABS(S1.c1-S2.c1) <= 0.5
AND ABS(S1.c2-S2.c2) <= 0.5
AND ABS(S1.c3-S2.c3) <= 0.5
AND ABS(S1.c4-S2.c4) <= 0.5
Unfortunately this query will not optimize well because of the join on a
calculated expression.
--
David Portas
SQL Server MVP
--
