Re: N rows in group query not working
Preserve formatting with BBCode code tags.
The inner query ...
select CountryId, ProductId, Revenue, cost, reg_date, sum(Revenue) as SumRevenue, (revenue/cost) as ratio
from financials, (Select @curCountryId :="", @curRow:=0) var
where ratio < 2
group by CountryId, ProductId
order by CountryId,SumRevenue desc
... has two fatal problems.
First, the Where clause cites the alias "ratio", which it can't see because SQL engines evaluate Where clauses before they evaluate Select expressions. So the query errors out. The result you've posted must have been from a different query.
The second problem is that in a query like ...
select a,b,c,d,sum(e)
...
group by a,b
values returned by c and d are undetermined, arbitrary. That's fatal for the accuracy of your query.
I haven't analysed your top-N-per-group logic. If results aren't as expected once you fix the above problems, post again.