Finding data according to relevency and then take 2 result from diff domain

Question

I am having so much domain name in my DB, for example

www.yahoo.com/duniya.html
www.yahoo.com/hero.html
www.123musiq.com/
www.123musiq.com/home.html
www.123musiq.com/horo.html
www.123musiq.com/yuva.html
www.sensongs.com/
www.sensongs.com/hindi.html
www.sensongs.com/yuva.html
www.sensongs.com/duniya.html
www.sensongs.pk/duniya1.html

i need to sort them like,first 2 from yahoo,then next 2 from 123musiq then next 2 from sensongs. how can i do that? in mysql or PHP? i know to get the domain name,i need to know how to sort 2 from each domain?
i am using match against method

Expecting output

 
    www.yahoo.com/duniya.html
    www.yahoo.com/hero.html
    www.123musiq.com/
    www.123musiq.com/home.html
    www.sensongs.com/
    www.sensongs.com/hindi.html

I Use following Code

SELECT x.url
  FROM (SELECT t.url,
               CASE 
                 WHEN @domain = SUBSTRING_INDEX(t.url, '/', 1) THEN @rownum := @rownum + 1
                 ELSE @rownum := 1
               END AS rank,
               @domain := SUBSTRING_INDEX(t.url, '/', 1)
          FROM URL2 t
          JOIN (SELECT @rownum := 0, @domain := '') r
      ORDER BY SUBSTRING_INDEX(t.url, '/', 1)) X
 WHERE x.rank <= 2 AND MATCH(teet,url,html) AGAINST ('account');

It Showing Error

Query : SELECT x.url   FROM (SELECT t.url,                CASE                   WHEN @domain = SUBSTRING_INDEX(t.url, '/', 1) THEN @row...
Error Code : 1054
Unknown column 'teet' in 'where clause'

But Teet is a Column in my URL2 Table

OMG Ponies OMG Ponies · Accepted Answer · 2010-08-20T04:14:21

MySQL doesn't have analytic functions, which is what you'd need to get the two entries per grouping. The other pain is the "www." complicates locating the 2nd period...

Use:

SELECT x.url
  FROM (SELECT t.url,
               t.teet,
               t.html,
               CASE 
                 WHEN @domain = SUBSTRING_INDEX(t.url, '/', 1) THEN @rownum := @rownum + 1
                 ELSE @rownum := 1
               END AS rank,
               @domain := SUBSTRING_INDEX(t.url, '/', 1)
          FROM URL2 t
          JOIN (SELECT @rownum := 0, @domain := '') r
      ORDER BY SUBSTRING_INDEX(t.url, '/', 1)) x
 WHERE x.rank <= 2

Finding data according to relevency and then take 2 result from diff domain

1 Answers