0

Hi guys i have an sql query string that in my view there is something wrong with it but i an handicaped since i am very new to sql so my question is, is there any way of optimizing this query string

select t1.cat,t1.site from (select cat,site from news where site='myjoy'and cat='business'limit 3) t1 union all
select t2.cat,t2.site from (select cat,site from news where site='pea'and cat='business'limit 3) t2 union all
select t3.cat,t3.site from (select cat,site from news where site='myjoy'and cat='sports' limit 3) t3 union all
select t4.cat,t4.site from (select cat,site from news where site='pea'and cat='sports'limit 3) t4 union all
select t5.cat,t5.site from (select cat,site from news where site='myjoy'and cat='ent'limit 3) t5 union all
select t6.cat,t6.site from (select cat,site from news where site='pea'and cat='ent' limit 3) t6 union all
select t7.cat,t7.site from (select cat,site from news where site='myjoy'and cat='cover_story' limit 3) t7 union all  
select t8.cat,t8.site from (select cat,site from news where site='pea'and cat='cover_story' limit 3) t8 union all
select t9.cat,t9.site from (select cat,site from news where site='pea'and cat='politics' limit 3) t9 union all
select t10.cat,t10.site from (select cat,site from news where site='myjoy'and cat='politics' limit 3) t10;
2
Contributors
1
Reply
19
Views
3 Years
Discussion Span
Last Post by cereal
1

Read this article:

Standing to the examples, you can try a query like this:

set @num := 0, @type := '';

select site, cat, title, content,
      @num := if(@type = concat(site, cat), @num + 1, 1) as row_number,
      @type := concat(site, cat) as dummy
from news
group by cat, site, title
having row_number <= 3;

Live example: http://sqlfiddle.com/#!9/097fc/10

Edited by cereal

Votes + Comments
i realy applicate the effore put into answering my questions thanks
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.