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 | I tried GROUP BY on the hash, but then MySQL grabs the earliest record. (Anyway to influence the sorting order before GROUP BY is applied?) E.g., | 000512 | 20031017010804 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000417 | 20031012135916 | 73641d1f174a502951db06653525af125dd4df46 | | 000188 | 20030920110057 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | | 000148 | 20030915094718 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | Alternatively, I also tried MAX(date), which of course is incorrect, because while returning the latest date for the hash--good!, it "looses touch" with the remaining columns of the record, i.e, ids and dates are mismatched, | outside provided sample | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | ^^^ ignore | 000417 | 20031012150600 | 73641d1f174a502951db06653525af125dd4df46 | | 000188 | 20030920135647 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | | outside provided sample | b8b993464ec303bbff704f582e4f7b5b9ea100bf | ^^^ ignore, again record outside the provided sample Am I missing some kind of magic somewhere or do I need to resort to extra columns and/or extra steps to get what I want? I am using 4.1.16 on Linux with 512 MB of RAM, so not sure if using intermediate tables would be a well performing solution, but if there is one, I still want to hear about it. :-) TIA, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
