Divide by zero error only with top

In the following expression, the query executes with no errors (there are sometimes 0's and nulls in IRON and ENERGY):

SELECT * FROM gff2vg

WHERE (gff2vg.ENERGY > 0 and gff2vg.IRON > 0)

order by IRON/ENERGY desc

but when TOP is added there is a divide by zero error. Is there a way to avoid this?

SELECT TOP(64) * FROM gff2vg

WHERE (gff2vg.ENERGY > 0 and gff2vg.IRON > 0)

order by IRON/ENERGY desc

Thanks.

This works but perhaps there is something more elegant:

select top (24) a.*, b.IRON/b.ENERGY as IRON2ENERGY

FROM gff2vg a,(SELECT * FROM gff2vg

WHERE (gff2vg.ENERGY > 0 and gff2vg.IRON > 0) ) b

WHERE a.UNIQID=b.UNIQID

order by IRON2ENERGY DESC

|||

If the field contains 0 or Null to avoid the divide by zero error always use case statement like this

SELECT TOP(64) * FROM gff2vg

WHERE (gff2vg.ENERGY > 0 and gff2vg.IRON > 0)

order by CASE ISNULL(ENERGY,'') WHEN 0
WHEN '' THEN 1
ELSE IRON/ENERGY
END desc

|||

The error has to do with SQL Server evaluating the expression IRON/ENERGY before the WHERE clause. There is no guarantee on the order in which various expressions in a SELECT statement (WHERE clause, SELECT list, ORDER BY, GROUP BY etc) are evaluated. See the link below for more details on the behavior change in SQL Server 2005:

http://msdn2.microsoft.com/en-us/library/ms143359.aspx

So the correct way to write the query is like:

SELECT *

FROM gff2vg

WHERE (gff2vg.ENERGY > 0 and gff2vg.IRON > 0)

order by IRON/nullif(ENERGY, 0) desc

-- or

SELECT TOP(64) * FROM gff2vg

WHERE (gff2vg.ENERGY > 0 and gff2vg.IRON > 0)

order by IRON/nullif(ENERGY, 0) desc

|||I only unmarked this as a solution because I am not sure of the nullif in the case ENERGY = null behavior and a null in IRON. The original intention is to get a recordset with no records that have IRON = null or any records with ENERGY = null or 0. In "ORDER BY IRON/nullif(ENERGY, 0)", if ENERGY = NULL or ENERGY=0->NULL via nullif(ENERGY,0), does IRON/null necessarily exclude the record? Also, if IRON = NULL, then the record is not of use either.|||

The expression in the ORDER BY clause has nothing to do with the predicates. It just ensures that you don't get overflow error in case of 0. NULL values of energy or iron will get filtered because of your WHERE clause. See Books Online for more details on what NULLIF does and how comparisons against NULL are handled. Using NULLIF is the easiest way to prevent divide by zero errors since it will result in the expression being NULL. You can use COALESCE around the entire expression if you want to assign a different default value like "coalesce(iron/nullif(energy, 0), -1).

|||Thank you for the explanation.