mercredi 4 février 2015

HAVING ANY in Postgres


I'm looking for the right syntax to filter groups (i.e. groups of rows that have been GROUP BY'd) where at least one row fits a certain condition.


For example if I wanted to select a group where at least one row has a fold_change value greater than 4, I would expect this to work:



HAVING ANY(rnaseq.fold_change) > 4


But I get the error



ERROR: syntax error at or near "ANY"





I suspect this is because Postgres requires the constant value to be on the left, but if I do it the other way around I get this error



4 < ANY(rnaseq.fold_change)



ERROR: op ANY/ALL (array) requires array on right side





The only way I can get it to work is using array_agg like this:



4 < ANY(array_agg(rnaseq.fold_change))


But that syntax is fairly confusing compared to the first example and having to manually create an array for each group doesn't sound efficient at all.


Is there a proper way to do HAVING ANY in postgres?





Aucun commentaire:

Enregistrer un commentaire