MySQL query, MAX() + GROUP BY

by

(Tested in PostgreSQL 9.something)

Identify the rid and timestamp.

select rid, max(timestamp) as ts
from test
group by rid;

1   2011-04-14 18:46:00
2   2011-04-14 14:59:00

Join to it.

select test.pid, test.cost, test.timestamp, test.rid
from test
inner join 
    (select rid, max(timestamp) as ts
    from test
    group by rid) maxt
on (test.rid = maxt.rid and test.timestamp = maxt.ts)

More Related Contents:

  1. How to store a one to many relation in MySQL database?
  2. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  3. Error Code: 2013. Lost connection to MySQL server during query
  4. MySQL Trigger after update only if row has changed
  5. difference between primary key and unique key
  6. Error creating table: You have an error in your SQL syntax near ‘order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ‘ at line 1 [duplicate]
  7. MySQL Query to select data from last week?
  8. Using an Alias in SQL Calculations
  9. Can a table field contain a hyphen?
  10. SQL: Creating a Relational table with 2 different auto_increment
  11. Adding a leading zero to some values in column in MySQL
  12. How do I add a custom CHECK constraint on a MySQL table?
  13. How to create relationships in MySQL
  14. View’s SELECT contains a subquery in the FROM clause
  15. How to insert data to MySQL with auto-incremented column(field)?
  16. Case statement in MySQL
  17. Replace Into Query Syntax
  18. How to produce “phantom read” in REPEATABLE READ? (MySQL)
  19. Laravel join with 3 Tables
  20. Difference between two table structure
  21. Alternative to using LIMIT keyword in a SubQuery in MYSQL
  22. How to insert a file in MySQL database?
  23. How to get multiple records against one record based on relation?
  24. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  25. Foreign key constraints: When to use ON UPDATE and ON DELETE
  26. How to use a user variables in MySQL LIKE clause?
  27. How to speed up sql queries ? Indexes?
  28. Selecting distinct 2 columns combination in mysql
  29. MySQL – Complexity of: SELECT COUNT(*) FROM MyTable;
  30. Inventory management with stock options

Leave a Comment