SQL Query for the closest value (HELP!!)-CodePudding

I have created a table on the database or I have loaded a caliber list with the corresponding prices. I need a request which when you enter a caliber (which is not frocally in the caliber table) it displays the price corresponding to the nearest key on the table of the one which was entered.

The table of sizes by price :

in an example, based on this the table of calibers. if I put the value 1.47 as the caliber, it must bring me the price corresponding to the 1.5 caliber. or if I put the value 1.41 as the caliber, it must bring me the price corresponding to the 1.4 caliber

CodePudding user response：

I would consider something like the following:

SELECT *
FROM
(
SELECT mt.*, RANK() OVER (ORDER BY ABS(caliber-1.41)) rn
FROM mytable mt
)
WHERE rn = 1

This calculates the difference between caliber and 1.41 using ABS for absolute value (to get closest without caring whether it is bigger or smaller). The WHERE rn = 1 then limits to the rows with the smallest difference.

Note that this assumes that if there are two rows that are equally far from your number, you want to return them both. If you want to arbitrarily pick one in the event of a tie I would replace RANK with ROW_NUMBER.

CodePudding user response：

The query does not work. because it does not recognize the "where rn"