CODEWITHSUNDEEP
mysql
Alan Hunt
Alan Hunt

Reputation: 19

Select multiple rows from distinct <column>

I've got a mysql table of 'events' with columns for 'category' and 'visited'. There's 3 categories an event can fall under. 'visited' counts how many times the event's page has been visited. I want the top two visited events from each of the 3 categories.

Here is an example table

id    category    visited
32   1               23
33   2               12
34   3               42
35   1               53
36   2               24
37   3               84
38   1               12
39   2               75
40   3               22

I need to display these rows in this order (id=): 35, 32, 39, 36, 37, 34. The code I have returns only 1 row from each category, and I believe is strictly limited to returning 1 row (I didn't write it.):

$categories=mysql_query("select distinct category from events");
while($row_cat=mysql_fetch_array($categories)){
    $row=mysql_fetch_array(mysql_query("select * from events where category='".$row_cat['category']."' order by visited desc"));
    echo $row['id'].", ";
}

That code displays: 35, 39, 37,

I feel as though the answer may be extremely simple, yet it is still eluding me. Thanks, in advance, for your help.

Upvotes: 1

Views: 1019

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

SELECT e.*
FROM 
        ( SELECT DISTINCT category
          FROM events 
        ) AS ed
    JOIN
        events AS e
            ON  e.category = ed.category
            AND e.visited >=
                ( SELECT visited 
                  FROM events AS ei
                  WHERE ei.category = ed.category
                  ORDER BY visited DESC
                  LIMIT 1 OFFSET 1
                )

Upvotes: 0

glglgl
glglgl

Reputation: 91017

What about

SELECT category, GROUP_CONCAT(id ORDER BY visited DESC)
    FROM events
    GROUP BY category

?

For your original code: It is because you only ask for one row.

Your while loop iterates over all categories. For each of them. it issues a query and asks for one row. You'd need 2 nested loop to query everything. So the "purely-MySQL" solution is by far better.

But if you need more than you stated originally, you probably better really do these queries one-by-one by nesting loops PHP-side:

$categories=mysql_query("select distinct category from events");
while($row_cat=mysql_fetch_array($categories)){
    $inner_res=mysql_query("select * from events where category='".mysql_real_escape_string($row_cat['category'])."' order by visited desc"); 
    while($inner_row=mysql_fetch_array($inner_res)){
        echo $inner_row['id'].", ";
    }
}

Upvotes: 1

dani herrera
dani herrera

Reputation: 51655

@glglgl method is really cool +1, also you can use find in set function to refine it and get first 3 events for each category one by row:

select e0.*
from 
   events e0
inner join
   (SELECT category, 
       GROUP_CONCAT(id ORDER BY visited DESC) as events
   FROM events
   GROUP BY category) C
     on
        e0.category = c.category and
        find_in_set( e0.event, c.events ) < 3

Be carefull, if you have a lot of events this can run slow.

Disclaimer: not tested.

Upvotes: 1

Related Questions