I am trying to merge the nested MYSQL queries into one

First of all I fire this query

select names , 
       city , 
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, 
       city , 
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

  • What's with all the single and triple quotes? Are these in your data or it's how you construct the query in python? Commented Jul 7, 2016 at 7:57
  • Yay for SQL Injection...
    – Philᵀᴹ
    Commented Jul 7, 2016 at 8:21

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.