Complex SQL Query with MySQL?

This query should work.

select * from (
    select a.state, a.location_id, C,
        @n:=case when @s=a.state then @n+1 else 1 end counter,
        @s:=a.state
    from (select @s:=null) b, (
        select pc.state, b.location_id, COUNT(b.location_id) C
        from postcode pc
        inner join location_postcode lp on lp.postcode_id=pc.id
        inner join business b on b.location_id=lp.location_id
        group by pc.state, b.location_id
        order by pc.state, C desc
    ) a
) c
where counter <= 10

I used field names that should be easy to follow, assuming these tables exist with stated relationships:

business M-1 location
location M-M postcode
    (expands to) => location 1-M location_postcode M-1 postcode
postcode M-1 state

The query has beentested with this data:

create table business (location_id int);
insert into business select floor(rand()*10);
insert into business select floor(rand()*10) from business;
insert into business select floor(rand()*10) from business;
insert into business select floor(rand()*10) from business;
insert into business select floor(rand()*10) from business;
insert into business select floor(rand()*10) from business;
insert into business select floor(rand()*10) from business;
insert into business select floor(rand()*10) from business;
insert into business select floor(rand()*10) from business;
insert into business select floor(rand()*10) from business;
create table location_postcode (location_id int, postcode_id int);
insert into location_postcode select 1,1;
insert into location_postcode select 2,1;
insert into location_postcode select 3,1;
insert into location_postcode select 4,2;
insert into location_postcode select 5,1;
insert into location_postcode select 5,2;
insert into location_postcode select 6,1;
insert into location_postcode select 6,3;
insert into location_postcode select 7,1;
insert into location_postcode select 7,4;
insert into location_postcode select 8,5;
insert into location_postcode select 9,6;
insert into location_postcode select 10,7;
create table postcode (id int, state int);
insert into postcode select 1,1;
insert into postcode select 2,2;
insert into postcode select 3,3;
insert into postcode select 4,4;
insert into postcode select 5,4;
insert into postcode select 6,5;
insert into postcode select 7,5;

Which doesn't quite create enough records for a "top 10" from each, but you will see how the COUNTER column is ranking correctly. To see it working against this small data set, first leave this filter in there

where counter <= 10

to check the COUNTER column, then reduce it to something like 2 or 3, to show only the top 2 or 3 per state.