Google Spreadsheet =query - ignore entities where cell is empty

Question

my starting table looks similar to the following

Person 1, 75
Person 2, 48 
Person 3,
Person 4, 82
Person 5,
Person 6, 93 
...

I now try to include in following query a "where" statement to exclude entities that have no numeric value. This is what I currently have to show me the lowest 5 values of the set above and it works so far

=QUERY('DPS Transpose Tables'!D1:E29;"select D, max(E) group by D order by max(E) asc limit 5 label max(E)  ''";0)

How can I add something like this that works

=QUERY('DPS Transpose Tables'!D1:E29;"select D  where (E<>"" OR Is not NULL), max(E) group by D order by max(E) asc limit 5 label max(E)  ''";0)

Thanks a million in advance!

Solution 1

Try 'where E >=0' like this:

=QUERY('DPS Transpose Tables'!D1:E29,"select D, max(E) where E >=0 group by D order by max(E) asc limit 5 label max(E)  ''",0)

Solution 2

You can also try 'WHERE IS NOT NULL'

Sometimes I find using the >= 0 does not get the desired results

=QUERY('DPS Transpose Tables'!D1:E29,"select D, max(E) where E IS NOT NULL group by D order by max(E) asc limit 5 label max(E) ''",0)

Comments

[Claus Maier]

Hi @Ed Nelson, awesome! Works perfectly, you saved my day. Thanks a lot!