CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Farhät
Farhät

Reputation: 61

SQL statement to find values close to each other

I'm struggling with defining the SQL to find a list of values that are statistically close to each other. For example, let's say we have a table of prices, and I want to get all the prices that vary within $0.25 of each other.

Prices: 1.00 1.25 2.00 4.00 4.50 4.75 5.00

For the example above, this should return 1.00, 1.25, 4.50, 4.75, and 5.00 as they are within 0.25 of another value in the list.

I don't want to get a raw list and then process it in the code. It would be much more efficient for SQL server to do the work. Is this possible?

Upvotes: 1

Views: 1090

Answers (3)

Karl Kieninger
Karl Kieninger

Reputation: 9129

I might use a correlated subquery:

DECLARE @tbl TABLE (val DECIMAL (9,2))
INSERT INTO @tbl VALUES (1),(1.25),(2),(4),(4.5),(4.75),(5)

SELECT * 
  FROM @tbl a 
 WHERE EXISTS(SELECT 1 
                FROM @tbl b 
               WHERE     b.val <> a.val 
                     AND b.val BETWEEN a.val-.25 AND a.val+.25)

You could also work an ABS into this which might be more succinct but probably doesn't impact performance:

SELECT * 
  FROM @tbl a 
 WHERE EXISTS(SELECT 1 
                FROM @tbl b 
               WHERE     b.val <> a.val 
                     AND ABS(b.val - a.val) <= .25)

EDIT: I switch from Float to Decimal, because that's a "better" type in SQL Server.

Upvotes: 2

Nizam
Nizam

Reputation: 4699

Try a join of the table with itself:

declare @Values table (value float)
insert into @Values values (1),(1.25),(2),(4),(4.5),(4.75),(5)

select distinct A.Value
from @values A
inner join @Values B
    on abs(A.value - B.Value) <= 0.25
    and A.Value <> B.Value

SQL Fiddle

OR in another approach, using CROSS APPLY

SELECT DISTINCT CASE WHEN N.n=1 THEN A.Value ELSE ant END
FROM @Values A
cross apply (select max(value) from @Values where Value < A.Value) B(ant)
CROSS APPLY(SELECT 1 UNION SELECT 2)N(n)
where abs(A.value - ant) <= 0.25

SQL Fiddle

And, if you are using SQL Server 2012+, you can use LEAD function:

SELECT DISTINCT CASE WHEN N.n=1 THEN A.Value ELSE ant END
FROM (
    SELECT Value, 
        LEAD(Value, 1,0) OVER (ORDER BY Value) AS Ant
    FROM @Values
) A
CROSS APPLY(SELECT 1 UNION SELECT 2)N(n)
where abs(Ant - Value) <= 0.25

SQL Fiddle

Upvotes: 5

Hozikimaru
Hozikimaru

Reputation: 1156

Try this;

DECLARE @TBL TABLE (Nums float)

insert into @TBL values (1),(1.25),(2),(4),(4.5),(4.75),(5);

WITH CTE AS(
Select  t1.Nums as T1,t2.Nums as T2 From @TBL t1
CROSS JOIN @TBL t2)
Select  distinct ct.T1 From CTE ct
where abs(ct.T1 - ct.T2) = 0.25
order by ct.T1

Upvotes: 0

Related Questions