SELECT MIN(m_over_z) AS Smallest, Max(m_over_z) AS Largest FROM tblPeptide WHERE m_over_z IN (Select m_over_z FROM tblPeptide WHERE m_over_z IS NOT NULL)

SELECT MIN(m_over_z) AS Smallest, Max(m_over_z) AS Largest FROM tblPeptide WHERE m_over_z IN (Select m_over_z FROM tblPeptide WHERE m_over_z IS NOT 'INF')

I have some columns which contain the result of a division.

Now sometimes that results in INF due to a division by zero (which is ok).

My problem is that I need the maximum value of a column that is not INF

This works fine:

SELECT MIN(m_over_z) AS Smallest, Max(m_over_z) AS Largest FROM tblPeptide

but when I try to do something like this

SELECT MIN(m_over_z) AS Smallest, Max(m_over_z) AS Largest FROM tblPeptide WHERE m_over_z IS NOT 'INF'

I still get INF

Any ideas on how to do this?
[/quote]
Depending on how you inserted this data for this column might be that whats stored is one of the binary representations of INF.
Its a specific bit pattern stored in a floating point value but to show it to you you see the string “INF”.
And when you select “INF” doesn’t actually match hence you get all the values.

Maybe

Dont insert values for the ones where the divisor is 0 (this is a trivial check)

Or insert 0 for them

Or have a flag column that you can check - IsINF - and set that to true when you have a divisor of 0

As Norm says, if the field is numeric, then IS NOT ‘INF’ is comparing a string to a number. Might work, but as you can see, it doesnt for you.
Im also dubious about IS NOT as opposed to <> IS NOT is usually only applicable to NIL in a SQL query, surely?

How about you think of an arbitrary large number and check against that?

SELECT MIN(m_over_z) AS Smallest, Max(m_over_z) AS Largest FROM tblPeptide WHERE m_over_z < 9999999999
group by something;