Select only last value using group by at mysql

by

Use a simple group by id_member, but select:

substring(max(concat(from_unixtime(timestamp),attendance)) from 20) as attendance

This attaches attendance to the timestamp for each row in a group, in order to be able to select the desired timestamp/attendance with max() and then extract just the attendance.

What concat() returns is 19 characters of formatted timestamp (YYYY-mm-dd HH:MM:SS) with the attendance appended starting at character 20; the substring(... from 20) gets just the attendance from the (stringwise) maximum one for the group. You can remove the group by and just

select concat(from_unixtime(timestamp),attendance), timestamp, attendance

to get a better idea of how it uses max to get the right attendance.

More Related Contents:

  1. Using LIMIT within GROUP BY to get N results per group?
  2. Error related to only_full_group_by when executing a query in MySql
  3. SELECT list is not in GROUP BY clause and contains nonaggregated column …. incompatible with sql_mode=only_full_group_by
  4. MySQL Query GROUP BY day / month / year
  5. MySQL order by before group by
  6. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  7. Grouping into interval of 5 minutes within a time range
  8. Retrieving the last record in each group – MySQL
  9. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  10. How to group by week in MySQL?
  11. MySQL “Group By” and “Order By”
  12. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  13. MySQL – Control which row is returned by a group by
  14. Group mysql query by 15 min intervals
  15. MySQL Order before Group by
  16. MySQL Group By and Sum total value of other column
  17. Is it possible to GROUP BY multiple columns using MySQL?
  18. Using Count to find the number of occurrences
  19. What does SQL clause “GROUP BY 1” mean?
  20. Using ORDER BY and GROUP BY together
  21. MySQL – Fetching lowest value
  22. ORDER BY date and time BEFORE GROUP BY name in mysql
  23. GROUP BY – do not group NULL
  24. MySQL GROUP BY DateTime +/- 3 seconds
  25. Sum results of a few queries and then find top 5 in SQL
  26. Select all months within given date span, including the ones with 0 values
  27. MySQL SELECT most frequent by group
  28. sql group by versus distinct
  29. mysql count group by having
  30. group by range in mysql

Leave a Comment