Gmail User wrote:
I am stomped and not sure how to get results from the particular type of
query. While I am not sure, if this is an appropriate place to ask, if
nothing else perhaps someone will direct me to a more appropriate forum.
I am trying to figure out how to return the latest record in each group
of records identified by some hash, e.g. (^ are rows I want),
^ 000543 | 20031019140457 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b |
| 000542 | 20031018214128 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b |
| 000525 | 20031018210622 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b |
| 000513 | 20031017010947 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b |
| 000512 | 20031017010804 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b |
^ 000431 | 20031012150600 | 73641d1f174a502951db06653525af125dd4df46 |
| 000417 | 20031012135916 | 73641d1f174a502951db06653525af125dd4df46 |
^ 000191 | 20030920135647 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f |
| 000188 | 20030920110057 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f |
^ 000161 | 20030917094352 | b8b993464ec303bbff704f582e4f7b5b9ea100bf |
| 000152 | 20030917084805 | b8b993464ec303bbff704f582e4f7b5b9ea100bf |
| 000151 | 20030915095857 | b8b993464ec303bbff704f582e4f7b5b9ea100bf |
| 000148 | 20030915094718 | b8b993464ec303bbff704f582e4f7b5b9ea100bf |
The result I want is:
| 000543 | 20031019140457 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b |
| 000431 | 20031012150600 | 73641d1f174a502951db06653525af125dd4df46 |
| 000191 | 20030920135647 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f |
| 000161 | 20030917094352 | b8b993464ec303bbff704f582e4f7b5b9ea100bf |
<....>
The trick I use in a situation like this is to LEFT JOIN the table to itself.
Suppose the three columns in the output you gave are "id INT", "time DATETIME",
and "csum VARCHAR" from the table "Checksums". I'd do something like:
SELECT c.*
FROM Checksums c
LEFT JOIN Checksums c2
ON c.csum = c2.csum AND c.time < c2.time
WHERE c2.id IS NULL
The ON clause of the LEFT JOIN will pair up each row of Checksums with
another row of Checksums sharing the same "csum", and having a more
recent "time". The records with the most recent "time" will be paired
with a NULL row, and selected for return by the WHERE clause.
-Maciej Babinski
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
