adamprocter adamprocter - 2 months ago 16
MySQL Question

Select data based on specific date and criteria MYSQL

What I want to do is select everything from the database for a specific dancer group that by date and then look for the latest date for each dance and only show those.

See attached image of what results (big red arrows) I would want to return from the database. I this example the single jig was not danced on the 11th of Sept but I need the last time this was danced (10th) so I can then calculate the current level a dancer is at for all dances.

example data I would want

Answer

Try

SELECT t.*
  FROM table1 t JOIN (
  SELECT dancer_id, dance, MAX(date) date
    FROM table1
   GROUP BY dancer_id, dance
) q ON t.dancer_id = q.dancer_id
   AND t.dance = q.dance
   AND t.date = q.date

Here is a SQLFiddle

Sample Output:

|  id | dancer_id |                        date |      dance | level | placed |  venue |
|-----|-----------|-----------------------------|------------|-------|--------|--------|
| 143 |       130 | September, 10 2016 00:00:00 | Single Jig |     1 |      4 | (null) |
| 146 |       130 | September, 11 2016 00:00:00 |       Reel |     2 |      5 | (null) |
| 147 |       130 | September, 11 2016 00:00:00 |  Light Jig |     2 |      4 | (null) |
| 148 |       130 | September, 11 2016 00:00:00 |   Slip Jig |     2 |      4 | (null) |
Comments