What SQL statement do I use to find what is the closest value in a data
set to the value I have.
For example I need to search in a column for the closest value that
exists to what I have:
E.g. my database has
X
1.2
1.3
2.6
1.0
2.5
1.4
1.7
I have 1.5 so I need to a query that return 1.4.
Any help would be appreciated
Thanks
TarryThis may not be very efficient unless you have an index on the column
X, but...
Could you query for 2 numbers, for a given SearchNumber to search for:
A. Largest number smaller than SearchNumber
B. Smallest number larger than SearchNumber
And then select whichever is closer to SearchNumber, A or B.
Basically:
SELECT
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
) T|||Lubdha Khandelwal wrote:
> This may not be very efficient unless you have an index on the column
> X, but...
> Could you query for 2 numbers, for a given SearchNumber to search for:
> A. Largest number smaller than SearchNumber
> B. Smallest number larger than SearchNumber
> And then select whichever is closer to SearchNumber, A or B.
> Basically:
> SELECT
> CASE
> WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
> T.Smaller
> ELSE T.Larger
> END
> FROM
> (
> SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
> FROM MyTable T1
> FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
> WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
> ) T
Lubha
I think we should remove <= from the query and join condition also
should be changed.
it should be
SELECT
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X < T2.X
WHERE T1.X < @.SearchNumber AND T2.X > @.SearchNumber
) T
Or
SELECT @.SearchNumber - T.Smaller,T.larger - @.SearchNumber,
CASE
WHEN (@.SearchNumber - T.Smaller < T.larger - @.SearchNumber ) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
select (select max(x) from mytable where x < @.searchnumber) smaller,
(select min(x) from mytable where x > @.searchnumber) larger
) T
Regards
Amish Shah|||> SELECT
> CASE
> WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
> T.Smaller
> ELSE T.Larger
> END
> FROM
> (
> SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
> FROM MyTable T1
> FULL OUTER JOIN MyTable T2 ON T1.X < T2.X
> WHERE T1.X < @.SearchNumber AND T2.X > @.SearchNumber
> ) T
>
But then you're not checking for equality. What if @.SearchNumber itself
exists in column X, shouldn't it return @.SearchNumber?|||Lubdha Khandelwal wrote:
> > SELECT
> > CASE
> > WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
> > T.Smaller
> > ELSE T.Larger
> > END
> > FROM
> > (
> > SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
> > FROM MyTable T1
> > FULL OUTER JOIN MyTable T2 ON T1.X < T2.X
> > WHERE T1.X < @.SearchNumber AND T2.X > @.SearchNumber
> > ) T
> >
>
> But then you're not checking for equality. What if @.SearchNumber itself
> exists in column X, shouldn't it return @.SearchNumber?
Ok , but for join also you should join it on < not on =
Here is gives null when using = for joins.
create table mytable(x decimal(10,2))
insert into mytable values(1)
insert into mytable values(2)
insert into mytable values(3)
insert into mytable values(4)
insert into mytable values(5)
declare @.searchnumber decimal(10,2)
set @.searchnumber = 2.5
SELECT t.smaller, t.larger,
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
) T
Regards
Amish Shah|||CREATE TABLE #temp (
Value NUMERIC(8, 2)
)
INSERT INTO #temp (Value) VALUES ( 1.2 )
INSERT INTO #temp (Value) VALUES ( 1.3 )
INSERT INTO #temp (Value) VALUES ( 2.6 )
INSERT INTO #temp (Value) VALUES ( 1.0 )
INSERT INTO #temp (Value) VALUES ( 2.5 )
INSERT INTO #temp (Value) VALUES ( 1.4 )
INSERT INTO #temp (Value) VALUES ( 1.7 )
DECLARE @.Target NUMERIC(8, 2)
SELECT @.Target = 1.5
SELECT *
FROM #temp
WHERE CAST(ABS(Value - @.Target) AS NUMERIC(8, 2)) = (SELECT
MIN(CAST(ABS(Value - @.Target) AS NUMERIC(8, 2))) FROM #temp)|||Actually, I came across another way to get the closest in a column...
SELECT TOP 1 *
FROM MyTable
ORDER BY ABS(X - @.SearchNumber) ASC|||Lubdha Khandelwal wrote:
> Actually, I came across another way to get the closest in a column...
> SELECT TOP 1 *
> FROM MyTable
> ORDER BY ABS(X - @.SearchNumber) ASC
Very nice! Painfully simple! Made me slap my forehead twice...
.pos = i + 1;