I'm trying to get the last matching in column 'more' of result VG1.
In this case my query is counting all the results in the column, but I need just to count before its changed. If its VG1, VG1, VG2, VG1 I need result 2, not 3 ( all ).
SELECT home, count(*) as count from results where more='VG2' and home='pero' group by home order by count(more) desc
You could use variables for this:
select home, more, count(*) as count from (select *, @set := if(@home <> home, '', @set), @home := home, not find_in_set(more, @set) as is_first, @set := if (@more <> '' and @more <> more, concat(@set, @more, ','), @set), @more := more from results, (select @set:='', @prev:='', @home='') init order by home, datum desc) base where is_first group by home, more order by count(*) desc
| home | more | count | |------|------|-------| | pero | VG1 | 2 | | pero | VG2 | 1 |
is_first column from the inner query indicates whether a record belongs to the first bunch of
more values per
home. This is put as a condition in the outer query, so that the
count function returns the required result.
If you need only the result for a certain
more value, then just add that in a
where clause in the outer query.