CODEWITHSUNDEEP
sqlvba
white_flag
white_flag

Reputation: 71

max nearest values sql

I have a table with some numerical values (diameters) 18 21 27 34 42 48 60 76 89 114 etc...

How Can I select the max nearest value if I put for example in a text.box a number. 25 to select 27, 100 to select 114, 48 to select 48.

I put the following code but it is not acting correct ...It is selecting the closest nearest value but not the MAX nearest value:

strSQL = "SELECT * " & "FROM [materials] WHERE ABS([dia] - " & Me.TextBox1.Text & ") = (SELECT MIN(ABS([dia] - " & Me.TextBox1.Text & ")) FROM [materials])"

this code is inside on an user form in excel that is connected to an DAO database.

Thank you!

Upvotes: 2

Views: 564

Answers (3)

Stefan Steiger
Stefan Steiger

Reputation: 82316

Actually, your problem description is wrong.
You don't want the closest nearest value, you want the minimum value of what's bigger or equal than requested.

The solution for what you actually requested would be:

DECLARE @fVal float
SET @fVal = 116 -- 114.5 -- 114.4 -- 114.6 

;WITH CTE AS
(
    SELECT 
          dia 
        ,(@fVal - dia) AS dist 
        ,ABS(@fVal - dia) AS absdist 
        ,SIGN(@fVal - dia) AS sig 
    FROM 
    ( 
        SELECT 18.0 AS dia 
        UNION SELECT 21.0 AS dia 
        UNION SELECT 27.0 AS dia 
        UNION SELECT 34.0 AS dia 
        UNION SELECT 42.0 AS dia 
        UNION SELECT 48.0 AS dia 
        UNION SELECT 60.0 AS dia 
        UNION SELECT 76.0 AS dia 
        UNION SELECT 89.0 AS dia 
        UNION SELECT 114.0 AS dia 
        UNION SELECT 115.0 AS dia 
    ) AS tempT 
) 

SELECT TOP 1 * FROM 
(
    SELECT * FROM CTE as cte2
    WHERE cte2.dist = (SELECT MAX(ct3.DIST) FROM CTE as ct3 WHERE sig = -1 )

    UNION 

    SELECT * FROM CTE as cte2
    WHERE cte2.dist = (SELECT MIN(ct3.DIST) FROM CTE as ct3 WHERE sig = 1) 


    UNION 

    SELECT * FROM CTE AS cte2 
    WHERE cte2.dist = 0 
) AS o

ORDER BY 

CASE WHEN dist = 0 
    THEN 0
    ELSE 1
END
,absdist, sig

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166486

Lets say you were using SQL Server, you could try something like

strSQL = "SELECT TOP 1 * " & "FROM [materials] WHERE [dia] >= " & Me.TextBox1.Text & " ORDER BY dia ASC"

If it was MySQL You would have to use LIMIT

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement.

Upvotes: 2

Linger
Linger

Reputation: 15068

strSQL = "SELECT TOP 1 * FROM materials " & _
         "WHERE dia >= " & Me.TextBox1.Text & " " & _
         "ORDER BY dia"

Upvotes: 2

Related Questions