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

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

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, privacy policy and cookie policy

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