Select the 3 most recent records where the values of one column are distinct

by

It doesn’t return what you expect because grouping happens before ordering, as reflected by the position of the clauses in the SQL statement. You’re unfortunately going to have to get fancier to get the rows you want. Try this:

SELECT *
FROM `table`
WHERE `id` = (
    SELECT `id`
    FROM `table` as `alt`
    WHERE `alt`.`otheridentifier` = `table`.`otheridentifier`
    ORDER BY `time` DESC
    LIMIT 1
)
ORDER BY `time` DESC
LIMIT 3

More Related Contents:

  1. MySQL order by before group by
  2. MySQL “Group By” and “Order By”
  3. MySQL – Control which row is returned by a group by
  4. MySQL Order before Group by
  5. ORDER BY date and time BEFORE GROUP BY name in mysql
  6. Error related to only_full_group_by when executing a query in MySql
  7. MySQL Query GROUP BY day / month / year
  8. MySQL ‘Order By’ – sorting alphanumeric correctly
  9. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  10. How to group by week in MySQL?
  11. GROUP_CONCAT ORDER BY
  12. What is MySQL row order for “SELECT * FROM table_name;”?
  13. Slow query when using ORDER BY
  14. Group mysql query by 15 min intervals
  15. MySQL: Sort GROUP_CONCAT values
  16. MySQL order by “best match”
  17. MySQL Group By and Sum total value of other column
  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. Returning query results in predefined order
  22. GROUP BY – do not group NULL
  23. Mysql delete statement with limit
  24. Sum results of a few queries and then find top 5 in SQL
  25. Select all months within given date span, including the ones with 0 values
  26. Sort the rows according to the order specified in WHERE IN clause
  27. MySQL SELECT most frequent by group
  28. Select only last value using group by at mysql
  29. group by range in mysql
  30. SQL query with avg and group by

Leave a Comment