asked
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
Define "best way"? Do you want the fastest-running query, or the query that's easiest to write & explain?
Please mark all answers that are helpful to you by clicking on the thumbs up next to those answers. If any answer solved your question, please indicate that one answer by clicking on the check box next to it.
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.
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
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.
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
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.
Nice idea. I suspect, though, that @Grant Fritchey's solution will be rather quicker, particularly if there's a suitable index.
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 ?
Try This declare @t1 table (C1 Decimal(6,3)) insert into @t1 values(1.234), (2.456), (5.789), (6.321) declare @v Decimal(6,3)=2.5 -- Get Two Row and 1 Columns Select MAX(c1) C1 FROM @t1 where c1-@v<0 union all Select Min(c1) C1 FROM @t1 where c1-@v>0 --- Get 2 Columns and 1 Row select MAX(T.c1) C1,MIN(T2.c1) C2 FROM @t1 T,@t1 t2 where t.C1-@v <0 and t2.C1-@v >0
18 People are following this question.
