MySQL query, Most recent values from a historical table of values

I have a table with historical values. Every time one of the value is updated a copy of that value is added to this table indexed by "request_id"

I am looking for away to get the most updated values for each "request_id" without having to retrieve the entire table.

Current table:

ID | Modified | request_id | value 
-------------------------------------
 1 |       10 |          1 | "one"
 2 |       15 |          1 | "two"
 3 |       15 |          2 | "three"
 4 |       18 |          1 | "fore"
 5 |       25 |          3 | "fiv开发者_StackOverflow社区e"
 6 |       36 |          1 | "six"

Result table

ID | Modified | request_id | value 
-------------------------------------
 3 |       15 |          2 | "three"
 5 |       25 |          3 | "five"
 6 |       36 |          1 | "six"

I am using PHP and SQLite if it makes any difference.

---

SQLite? Hmz, can't test but try this:

SELECT value FROM your_table GROUP BY request_id ORDER BY modifier;

If you have any issues with this query, post the error and we'll try and fix.

---

I may have the syntax slightly wrong but this should point you in the right direction.

"SELECT DISTINCT request_id FROM history_table ORDER BY modified"

This will only grab one of each request_id so you do not get duplicates.

---

SELECT *
FROM test_table t1
WHERE t1.modified IN (SELECT max(Modified)
                      FROM test_table t2
                      WHERE t2.request_id = t1.request_id
                      GROUP By t2.request_id)

Note: this is not a very efficient way of doing it, since it has correlated subquery.

EDIT: without correlation, for PostgreSQL

SELECT * FROM test
WHERE (request_id, modified) IN (SELECT request_id, max(modified)
                                 FROM test
                                 GROUP BY request_id);