I'm currently trying to write a SQL query, that get's the top 100 rows with the biggest percent increase over two values. My current solution is to write my query like this:
//NULL is also not accepted in where clause, shortened version
SELECT TOP 100 id, name, currentValue, pastDayValue FROM comparables
WHERE NOT pastDayValue = currentValue AND NOT pastDayValue = 0 AND NOT currentValue = 0
ORDER BY (currentValue - pastDayValue) / (currentValue / 100) desc;
The only issue is: it can occur that the values are zero or the same, and altough I do want to filter these out (see where clause), I still get a System.Data.SqlException 'Divide by zero' which has to come from the order by clause.
I've done some research, and as far as I've found, the where clause should be executed before the order by, therefore filtering any zero values out (the tables can also have NULL values which I do escape in the where clause aswell, just didn't include it into the query above to make it shorter). The only ways I saw to prevent this, are either NULLIF or a CASE clause, but I'm not sure how to work those into my order by statement.
I'd appreciate any ideas on how to solve this! (Note: I'm working on SQL-Server 2019)
CodePudding user response：
This is possibly caused by dividing an Integer-value (currentValue) by an Integer-value. If an Integer dividend is divided by an Integer divisor, the decimals will be truncated.
A possible solution would be to change the Integer 100 to a Decimal number like this:
ORDER BY (currentValue - pastDayValue) / (currentValue / 100.0) desc;
CodePudding user response：
ORDER BY (currentValue - pastDayValue) / (currentValue / 100.0) desc;
eq
ORDER BY (currentValue - pastDayValue) / (currentValue) desc;
eq
ORDER BY (currentValue) / (currentValue) - (pastDayValue) / (currentValue) desc;
eq
ORDER BY 1 - pastDayValue / currentValue desc;
eq
ORDER BY pastDayValue / currentValue asc;