Select multiple rows from distinct <column>

Question

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.

glglgl · Accepted Answer · 2012-03-27 12:07:39Z

1

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'].", ";
    }
}

edited Mar 27, 2012 at 12:07

answered Mar 27, 2012 at 11:21

glglgl

91.5k13 gold badges157 silver badges230 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alan Hunt Over a year ago

Sorry, I might not have explained myself properly. I don't need to just display the IDs of the 6 rows, I need to iterate through the top 2 events from each category so I can display values in fields such as title and description. Sort of like an RSS feed if you will.

Alan Hunt Over a year ago

The inner while loop doesn't seem to be executing. EDIT: derp, copy pasta only works when you edit back in appropriate values, like the name of the table.

Alan Hunt Over a year ago

Thanks, that worked. All I have to do is limit the inner loop to 2 results.

dani herrera · Accepted Answer · 2012-03-27 11:36:56Z

1

@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.

answered Mar 27, 2012 at 11:36

dani herrera

52k11 gold badges133 silver badges188 bronze badges

1 Comment

Alan Hunt Over a year ago

I like where you're going with that, however I do plan to have this table become very large, so is there a way to do this other than using find_in_set?

ypercubeᵀᴹ · Accepted Answer · 2012-03-27 12:22:16Z

0

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
                )

answered Mar 27, 2012 at 12:22

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Collectives™ on Stack Overflow

Select multiple rows from distinct <column>

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related