Working on lowest price in last 30 days, I encountered an error and with Jurica's help it was fixed. To briefly cover what it is, the <> (not equal to) in SQL query wasn't working as expected, giving wrong results even though the logic and the code were correct. Apart from this explanation, it can also be sorted by converting to floats or ints everywhere.
This is the cause:
It sounds like you encountered an issue with floating-point precision, which is common when working with decimal values in programming. Here's a brief explanation of why this could happen:
Floating-Point Precision
- Precision Issues: Floating-point numbers (like 0.1, 0.15, etc.) can't always be represented exactly in binary, leading to very small errors in calculations. These errors can cause unexpected behavior when comparing two floating-point numbers that should theoretically be equal.
- Equality Comparison (= and <>): When you compare two floating-point numbers for equality, the small precision errors might make two numbers that should be equal appear different. Conversely, numbers that seem different might appear equal due to these small errors. This explains why using <> (not equal to) might have caused unexpected results.
Why Your Solution Worked
By offsetting the price by 1 cent and using a range comparison (< and >, with the offset), you effectively avoided the precision problem. This approach ensures that you're not relying on direct equality checks, which can be unreliable with floating-point numbers.
This is a clever and practical workaround, and it's great that you found a solution that works for your case!
If you have any more questions or need further assistance, feel free to ask; otherwise, I'm glad everything's working as expected now.