Multiple HAVING clause?

I’ve trying to figure out to do a query with multiple HAVING.

 select grid_reference,title,count(*) as cnt,max(id) as upd 
 from sample8 group by grid_reference,title having cnt > 1 and upd>=6753064; 

sphinxql: syntax error, unexpected AND, expecting $end near ‘and upd>=6753064’

(to explain, looking for ‘duplicate’ rows (based on grid_reference and title) so count>1, but also only where one of the IDs in the set is above a certain threshold. ie only one, or more, of ids, not all, so can’t use WHERE)

Either having clause, works on its own, Because both criteria are aggregates (count/max), can’t just create a compound colume

select grid_reference,title,count(*) as cnt,max(id) as upd,if(cnt > 1 and upd>=6753064,1,0) as hav
from sample8 group by grid_reference,title having hav=1;

index sample8: parse error: unknown column: cnt

I don’t think REMOVE_REPEATS works, as want the actual count of duplicates. (and want the duplicates, not the singles)

Its a big query, 6.1M rows without the having. So cutting down on the data transferred out of manticore is important.

it could be better to create feature request at Github as currently HAVING is only single filter condition but we could implement complete filter tree as at WHERE clause

Ok, will do that. Was mostly hoping (perhaps unrealistically!) for a workaround in the meantime :slight_smile:

no workaround for that as it was implemented as single field of filter item but even having cnt > 1 and upd>=6753064 needs array of filter items that is why you can not use multiple filter conditions there for now