Max but not INF?

Databases
1

Hi,

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?

TiA

Markus

2

Tried

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

but then I get

Mass range [m/z] with tags: 234,658 to 1,

while

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

results in DB Error: near “INF”: syntax error

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

should work as far as I can see but doesn’t.

4 
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)

still returns INF …

5

as does

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')

6

Hmmm, this

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

finally returns a value

Mass range [m/z] with tags: 234,658 to 234,658

but it is the minimum …

7

[quote=131426:@Markus Winter]Hi,

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
8

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;