Mysql – Merge sub-queries to a single query

MySQLsubquery

I am trying to merge the nested MYSQL queries into one

First of all I fire this query

select names , city , category from names_data group by names , city ,category ;

this produces a result of ~28000 from master names_data table

now for each entry in the result
I fire this query inside a python loop

select date, name, city , old_mul, new_mul from name_changes where name=' + name + ' and city=' + city + """' order by date desc limit 2 ;

I just want the last 2 records date wise for each name and city combination from this name_changes table

I have tried few JOINS and groups operation but was not able to achieve what this

Right Now I have 1 + 28000*1 queries

I want to reduce the queries

Help is really appreciated 🙂

p.s. the tables are different

Best Answer

This should work:

-- Query every name_changes where rownum <= per group limit (2)
select * from (
    -- subquery for attaching rownumber counter for `name:city` groups
    select `date`, name, city , old_mul, new_mul, 
        case when @lag != concat(name, ':', city) then @rownum:=1 else @rownum:=@rownum+1 end as rownum, 
        @lag:=concat(name, ':', city)
    from name_changes,
    -- init values
    (select @rownum:=0) init_rownum, 
    (select @lag:=null) init_la
    order by name, city, d desc
) numbered
where rownum <= 2;

I had a previous answer about mimicking window functions in mysql: How do I write this query? where I explained this in more details.

Related Question