title: Select only last value using group by at mysql
body: <p>I have one table with data about attendance into some events. I have in the table the data of the attendance everytime the user sends new attendance, the information is like this:</p>
  
  <pre><code>mysql&gt; SELECT id_branch_channel, id_member, attendance, timestamp, id_member FROM view_event_attendance WHERE id_event = 782;
  +-------------------+-----------+------------+------------+-----------+
  | id_branch_channel | id_member | attendance | timestamp  | id_member |
  +-------------------+-----------+------------+------------+-----------+
  |              1326 |    131327 |        459 | 1363208604 |    131327 |
  |              1326 |    131327 |        123 | 1363208504 |    131327 |
  |              1326 |    131327 |          1 | 1363208459 |    131327 |
  |              1326 |     93086 |          0 |       NULL |     93086 |
  |              1326 |     93087 |          0 |       NULL |     93087 |
  |              1326 |     93088 |          0 |       NULL |     93088 |
  |              1326 |     93093 |          0 |       NULL |     93093 |
  |              1326 |     99113 |          0 |       NULL |     99113 |
  |              1326 |     99135 |          0 |       NULL |     99135 |
  |              1326 |     99199 |          0 |       NULL |     99199 |
  |              1326 |     99200 |          0 |       NULL |     99200 |
  |              1326 |    131324 |          0 |       NULL |    131324 |
  |              1326 |     85850 |          0 |       NULL |     85850 |
  |              1326 |     93085 |          0 |       NULL |     93085 |
  +-------------------+-----------+------------+------------+-----------+
  14 rows in set (0.00 sec)
  </code></pre>
  
  <p>(This is actually a view, for that reason some of the fields are null).</p>
  
  <p>I can groupby id_member so I get only one row for every member (that is, only the last attendance the user sent). However, when I do it, I received the first attendance the user sent, not the last one.</p>
  
  <pre><code>mysql&gt; SELECT id_branch_channel, id_member, attendance, timestamp, id_member FROM view_event_attendance WHERE id_event = 782 GROUP BY id_event,id_member;
  +-------------------+-----------+------------+------------+-----------+
  | id_branch_channel | id_member | attendance | timestamp  | id_member |
  +-------------------+-----------+------------+------------+-----------+
  |              1326 |    131327 |          1 | 1363208459 |    131327 |
  |              1326 |     93086 |          0 |       NULL |     93086 |
  |              1326 |    131324 |          0 |       NULL |    131324 |
  |              1326 |     93087 |          0 |       NULL |     93087 |
  |              1326 |     93088 |          0 |       NULL |     93088 |
  |              1326 |     93093 |          0 |       NULL |     93093 |
  |              1326 |     99113 |          0 |       NULL |     99113 |
  |              1326 |     99135 |          0 |       NULL |     99135 |
  |              1326 |     85850 |          0 |       NULL |     85850 |
  |              1326 |     99199 |          0 |       NULL |     99199 |
  |              1326 |     93085 |          0 |       NULL |     93085 |
  |              1326 |     99200 |          0 |       NULL |     99200 |
  +-------------------+-----------+------------+------------+-----------+
  12 rows in set (0.00 sec)
  </code></pre>
  
  <p>I already tried to add ORDER BY clausules, but they are not working at all... any ideas?</p>
  
  <p>Thanks in advance!</p>
  
  <p><strong>Edit</strong>: this is the script that creates the table</p>
  
  <pre><code>CREATE OR REPLACE VIEW view_event_attendance 
      AS
          SELECT 
              tbl_event.id_event,
              tbl_member_event.id_member,
              tbl_event.id_branch_channel,
              tbl_member_event_attendance.id_member_event_attendance,
              IF(ISNULL(tbl_member_event_attendance.attendance), 0, tbl_member_event_attendance.attendance) AS attendance,
              tbl_member_event_attendance.timestamp
          FROM 
              tbl_event
              INNER JOIN 
                  tbl_member_event ON tbl_member_event.id_event = tbl_event.id_event
                  LEFT OUTER JOIN
                      tbl_member_event_attendance ON tbl_member_event_attendance.id_member_event = tbl_member_event.id_member_event
          ORDER BY 
              tbl_member_event_attendance.timestamp DESC;
  </code></pre>
  
  <p><strong>EDIT 2:</strong></p>
  
  <p>Thanks a lot MichaelBenjamin, but the problem when using subqueries is the size of the view:</p>
  
  <pre><code>mysql&gt; DESCRIBE SELECT id_branch_channel, id_member, attendance, timestamp, id_member 
      -&gt; FROM (select * from view_event_attendance order by timestamp desc) as whatever
      -&gt; WHERE id_event = 782 
      -&gt; GROUP BY id_event,id_member;
  +----+-------------+-----------------------------+--------+-----------------+-----------------+---------+------------------------------------------------+-------+----------------------------------------------+
  | id | select_type | table                       | type   | possible_keys   | key             | key_len | ref                                            | rows  | Extra                                        |
  +----+-------------+-----------------------------+--------+-----------------+-----------------+---------+------------------------------------------------+-------+----------------------------------------------+
  |  1 | PRIMARY     | &lt;derived2&gt;                  | ALL    | NULL            | NULL            | NULL    | NULL                                           | 16755 | Using where; Using temporary; Using filesort |
  |  2 | DERIVED     | tbl_member_event            | index  | id_event        | id_event        | 8       | NULL                                           | 16346 | Using index; Using temporary; Using filesort |
  |  2 | DERIVED     | tbl_event                   | eq_ref | PRIMARY         | PRIMARY         | 4       | video_staging.tbl_member_event.id_event        |     1 |                                              |
  |  2 | DERIVED     | tbl_member_event_attendance | ref    | id_event_member | id_event_member | 4       | video_staging.tbl_member_event.id_member_event |     1 | Using index                                  |
  +----+-------------+-----------------------------+--------+-----------------+-----------------+---------+------------------------------------------------+-------+----------------------------------------------+
  4 rows in set (0.08 sec)
  </code></pre>
  
  <p>As you can see there are a lot of rows in my table, so for that reason I don't want to use subqueries...</p>
  
  <p><strong>EDIT 3:</strong></p>
  
  <p>But adding WHERE to the subquery it looks better...</p>
  
  <pre><code>mysql&gt; DESCRIBE SELECT id_branch_channel, id_member, attendance, timestamp, id_member 
      -&gt; FROM (select * from view_event_attendance where id_event = 782 order by timestamp desc) as whatever
      -&gt; WHERE id_event = 782 
      -&gt; GROUP BY id_event,id_member;
  +----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+----------------------------------------------+
  | id | select_type | table                       | type  | possible_keys   | key             | key_len | ref                                            | rows | Extra                                        |
  +----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+----------------------------------------------+
  |  1 | PRIMARY     | &lt;derived2&gt;                  | ALL   | NULL            | NULL            | NULL    | NULL                                           |   14 | Using where; Using temporary; Using filesort |
  |  2 | DERIVED     | tbl_event                   | const | PRIMARY         | PRIMARY         | 4       |                                                |    1 | Using temporary; Using filesort              |
  |  2 | DERIVED     | tbl_member_event            | ref   | id_event        | id_event        | 4       |                                                |   12 | Using index                                  |
  |  2 | DERIVED     | tbl_member_event_attendance | ref   | id_event_member | id_event_member | 4       | video_staging.tbl_member_event.id_member_event |    1 | Using index                                  |
  +----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+----------------------------------------------+
  4 rows in set (0.01 sec)
  </code></pre>
  
  <p>If I can not find anything else not using subqueries, I think I'll choose this as the answer...</p>
  
  <p><strong>Edit 4</strong></p>
  
  <p>After seeing the comments in the answer, I've decided to select another as the answer. Here is the DESCRIBE for both queries, and I think it is obvious what is the best solution:</p>
  
  <pre><code>mysql&gt; DESCRIBE SELECT 
      -&gt;   id_branch_channel,
      -&gt;   id_member, 
      -&gt;   attendance, 
      -&gt;   timestamp,
      -&gt;   id_member
      -&gt; FROM view_event_attendance AS t1
      -&gt; WHERE id_event = 782
      -&gt; AND timestamp = (SELECT MAX(timestamp)
      -&gt;                  FROM view_event_attendance AS t2 
      -&gt;                  WHERE t1.id_member = t2.id_member 
      -&gt;                    AND t1.id_event = t2.id_event 
      -&gt;                  GROUP BY id_event, id_member)
      -&gt; OR timestamp IS NULL
      -&gt; GROUP BY id_event, id_member;
  +----+--------------------+-----------------------------+--------+--------------------+--------------------------+---------+------------------------------------------------+------+-----------------------------------------------------------+
  | id | select_type        | table                       | type   | possible_keys      | key                      | key_len | ref                                            | rows | Extra                                                     |
  +----+--------------------+-----------------------------+--------+--------------------+--------------------------+---------+------------------------------------------------+------+-----------------------------------------------------------+
  |  1 | PRIMARY            | tbl_event                   | index  | PRIMARY            | id_member_branch_channel | 4       | NULL                                           |  208 | Using index; Using temporary; Using filesort              |
  |  1 | PRIMARY            | tbl_member_event            | ref    | id_event           | id_event                 | 4       | video_staging.tbl_event.id_event               |   64 | Using index                                               |
  |  1 | PRIMARY            | tbl_member_event_attendance | ref    | id_event_member    | id_event_member          | 4       | video_staging.tbl_member_event.id_member_event |    1 | Using where; Using index                                  |
  |  2 | DEPENDENT SUBQUERY | tbl_event                   | eq_ref | PRIMARY            | PRIMARY                  | 4       | func                                           |    1 | Using where; Using index; Using temporary; Using filesort |
  |  2 | DEPENDENT SUBQUERY | tbl_member_event            | eq_ref | id_event,id_member | id_event                 | 8       | video_staging.tbl_event.id_event,func          |    1 | Using where; Using index                                  |
  |  2 | DEPENDENT SUBQUERY | tbl_member_event_attendance | ref    | id_event_member    | id_event_member          | 4       | video_staging.tbl_member_event.id_member_event |    1 | Using where; Using index                                  |
  +----+--------------------+-----------------------------+--------+--------------------+--------------------------+---------+------------------------------------------------+------+-----------------------------------------------------------+
  6 rows in set (0.00 sec)
  
  
  mysql&gt; DESCRIBE SELECT *
      -&gt; FROM (SELECT id_branch_channel, id_member, attendance, timestamp, id_event 
      -&gt;       FROM view_event_attendance 
      -&gt;       WHERE id_event = 782 
      -&gt;       ORDER BY timestamp desc
      -&gt;      ) as whatever
      -&gt; GROUP BY id_event,id_member;
  +----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+---------------------------------+
  | id | select_type | table                       | type  | possible_keys   | key             | key_len | ref                                            | rows | Extra                           |
  +----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+---------------------------------+
  |  1 | PRIMARY     | &lt;derived2&gt;                  | ALL   | NULL            | NULL            | NULL    | NULL                                           |   14 | Using temporary; Using filesort |
  |  2 | DERIVED     | tbl_event                   | const | PRIMARY         | PRIMARY         | 4       |                                                |    1 | Using temporary; Using filesort |
  |  2 | DERIVED     | tbl_member_event            | ref   | id_event        | id_event        | 4       |                                                |   12 | Using index                     |
  |  2 | DERIVED     | tbl_member_event_attendance | ref   | id_event_member | id_event_member | 4       | video_staging.tbl_member_event.id_member_event |    1 | Using index                     |
  +----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+---------------------------------+
  4 rows in set (0.00 sec)
  </code></pre>