Selecting all corresponding fields using MAX and GROUP BY

by

without a single primary key field, I think your best bet is:

select * from deal_status
inner join
  (select deal_id as did, max(timestamp) as ts
  from deal_status group by deal_id) as ds
  on deal_status.deal_id = ds.did and deal_status.timestamp = ds.ts

this still won’t work if you allow having two different statuses for the same product at the same time

More Related Contents:

  1. Select first row in each GROUP BY group?
  2. Using LIMIT within GROUP BY to get N results per group?
  3. How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
  4. GROUP BY with MAX(DATE) [duplicate]
  5. How to SELECT the newest four items per category?
  6. Return row with the max value of one column per group [duplicate]
  7. Group by minimum value in one field while selecting distinct rows
  8. Retrieving the last record in each group – MySQL
  9. SQL: Find the max record per group [duplicate]
  10. Simple Query to Grab Max Value for each ID
  11. Group by month and year in MySQL
  12. In SQL, how to select the top 2 rows for each group
  13. SQL Group by & Max
  14. SQL Query to get column values that correspond with MAX value of another column?
  15. mySQL Returning the top 5 of each category
  16. Using ORDER BY and GROUP BY together
  17. Mysql: how to select groups having certain values?
  18. MySQL #1140 – Mixing of GROUP columns
  19. SQL not a single-group group function
  20. MySQL GROUP BY behavior
  21. Get values from first and last row per group
  22. Delete from two tables in one query
  23. Select info from table where row has max date
  24. MySQL’s now() +1 day
  25. Rewriting mysql select to reduce time and writing tmp to disk
  26. How to create a multi-tenant database with shared table structures?
  27. How can I select rows with most recent timestamp for each key value?
  28. MySQL: “The SELECT would examine more than MAX_JOIN_SIZE rows”
  29. what is the difference between GROUP BY and ORDER BY in sql
  30. Why doesn’t Oracle SQL allow us to use column aliases in GROUP BY clauses?

Leave a Comment