# question

## Retrieve closest upper and lower values

Hi, I have a table, T1, with a column, C1, of decimal values. C1 1.234 2.456 5.789 6.321 I want to write a query, where the results are the 2 closest records (1 closest above and 1 closest below) a given value. With the data in C1 as outlined above, if my variable contains the value 2.5, I want the query to return 5.789 (closest number greater than my variable value), and 2.456 (closest number less than my variable value). What is the best way to write a query to generate this behaviour? Thanks in advance, Darrin

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Define "best way"? Do you want the fastest-running query, or the query that's easiest to write & explain?
1 Like 1 ·
0 Likes 0 ·
You need to handle cases where the value being queried actually matches one of the values in your table. Currently answers are handling this in different ways. Review them and pick the one that suits your needs. You also need to vote one of the answers as the one that solved your problem.
0 Likes 0 ·

·
Hmmm... Never tried something like this before, but I think you could use a UNION ALL & two steps like this: DECLARE @myVariable INT = 42; SELECT * FROM ( SELECT TOP ( 1 ) * FROM dbo.OfferItems AS oi WHERE ListID @myvariable ORDER BY ListID ASC ) AS x
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sorry to be pedantic here but this code wont necessarily work as expected. When comparing a DECIMAL(I guess something like (8,3) but the OP doesnt state the datatype in question) and an INT, the comparison will not be accurate. To see this place the value 2.8 in your @myVariable. You would expect to see results of 2.456 and 5.789 but the implicit conversion of the DECIMAL to INT means the results returned are 1.234 and 2.456. The resolution here needs to be declaring @myVariable as a decimal too. This will result in no conversion for the comparison and the code will return the results that are expected.
0 Likes 0 ·
How about this as an alternative declare @myvalue decimal(6,3); set @myvalue = 2.5; select case sign(C1-@myvalue) when 1 then min(C1) else max(C1) end as C1 from ( values(1.234),(2.456),(5.789),(6.321) ) AS x(C1) group by sign(C1-@myvalue) having sign(C1-@myvalue)0

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I didn't test it beyond validating that it worked. I just wanted to see if I could put together a functional solution. I'm sure there are more effective ways than either of us has posted so far.
1 Like 1 ·
Nice idea. I suspect, though, that @Grant Fritchey's solution will be rather quicker, particularly if there's a suitable index.
0 Likes 0 ·
Yes, I agree - but are you likely to have an index on a decimal column? I did test performance on a 1 million row table that had just that one column and Grant's was definitely quicker for that - not by an awful lot - but quicker. The IO values were pretty much the same as both required only 1 physical scan - the results of that scan being cached for Grant's second scan. Maybe there would be a tipping point ?
0 Likes 0 ·