Select recent events with all activities listed SQL

Question

I have a database that will store events. Each event has different activities. The database is made as so (I know this is not the best database design, but I am working with what was given to me) :

eventID | date       | eventname | activity
___________________________________________
0001    | 01/01/2012 | name1     | activty1
0002    | 01/01/2012 | name1     | activty2
0003    | 01/05/2012 | event2    | activty1
0004    | 01/05/2012 | event2    | activty2
0005    | 01/07/2012 | name1     | activty1

The same event can have multiple activities. The only way I can get the all records from an event (activities) is by the eventname column.

What I am trying to do is get the last 20 events with all it's activities. Therefore I cannot simply select 20 rows (using LIMIT as this would not get me 20 events everytime as some events have 2 activities and others 10).

I cannot use SELECT DISTINCT eventname because some events have same name, on different dates. What I would need is to have a distinct eventname only if the date is different.

I'm lost for ideas.

Thanks.

Do you want the last 20 events but each eventname can only be represented once? — Conrad Frix, Feb 24 '12 at 16:18
@ConradFrix I am looking for 20 events, with all their activities. I am looking to get basically x number of records that represent 20 events. - hopefully this makes sense. — blo0p3r, Feb 24 '12 at 17:01
Could you query the last 20 events, and then loop around them in a cursor, looking up all the activities for each one, then inserting those into a table object, and then returning those rows? Possibly not the fastest way to do it, but it would be pretty straightforward. — Ted, Feb 24 '12 at 17:17
Look at the first answer to this question http://stackoverflow.com/questions/649802/how-to-pivot-a-mysql-entity-attribute-value-schema It appears to use a MySQL command I'm not familiar with, but should satisfy your needs. — Sam DeHaan, Feb 24 '12 at 21:06

MatBailie · Accepted Answer · 2012-02-24T20:57:10.103

2

SELECT
  *
FROM
  event
INNER JOIN
(
  SELECT   eventName, Date
  FROM     events
  GROUP BY eventName, Date
  ORDER BY Date DESC
  LIMIT    20
)
  AS last_20_events
    ON  last_20_events.eventName = events.eventName
    AND last_20_events.Date      = events.Date

edited Feb 24 '12 at 20:57

answered Feb 24 '12 at 16:44

MatBailie

83,401
18
103
137

Close, and this makes sense, but my eventIDs are all different. The eventIDs are different for every entry in the database. I've edited the questions slightly to display this. – blo0p3r Feb 24 '12 at 16:56
This here gave me the last 20 records, but it will be more then 20 records, since I want 20 events, not 20 activities. – blo0p3r Feb 24 '12 at 17:00
just change the eventID references to eventName. – Conrad Frix Feb 24 '12 at 17:11
@blo0p3r - No idea what your last comment meant, but I've updated the answer. – MatBailie Feb 24 '12 at 21:01
Sorry about the last comment. Bug with browser : Thanks... this did most of the work... had to figure out a few things, but for the purpose of this question this did help... I only needed to add restrictions for dates. – blo0p3r Feb 24 '12 at 21:56

score 0 · Answer 2 · answered Feb 24 '12 at 21:13

SELECT eventName, date, 
 GROUP_CONCAT(activity ORDER BY activity SEPARATOR ', ') 
FROM events
GROUP BY eventName, date
ORDER BY date desc
LIMIT 20

I don't have access to a MySQL DB at the moment, so I haven't been able to test this. This answer is based on other accepted answers on SO (search for group_concat)

Select recent events with all activities listed SQL

2 Answers2