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.
