[RESOLVED] mySQL search help

NZ_Kiwis

I have a table where i have a topicID and a replyID

I want to search for the latest replyID for each topicID and limit this to 10.

This means my 10 displayed replyID's will all have different topicID's

HOw can I do this?

Derokorian

SELECT replyID, topicID FROM table GROUP BY topicID ORDER BY datetime DESC LIMIT 1

Something like that should work? Without seeing the actual table structure or at least some sample data that's the best I got. HTH

johanafm

Derokorian;10997657 wrote:
SELECT replyID, topicID FROM table GROUP BY topicID ORDER BY datetime DESC LIMIT 1

The SQL query is invalid. While MySQL will accept it, there is no guarantee that you will get the results you are expecting, since the SQL spec (in this part also followed by MySQL) states that there never is an order to a table unless an ORDER BY clause is given, which in effect means that the above query may return any replyID, not necessarily the lowest or highest, but anything in between as well.

Sure, the latest datetime is likely to correspond to MAX(replyID), but without constraints on the table, there really is no way to be certain. So, if you really want to write invalid! and non-portable! code, when the correct alternative is as easy to write, at least ORDER BY replyID DESC. This will at least get you the correct data for now... Some future version might no longer work at all or produce different results.

Solution is to rewrite it to be valid SQL while ascertaining that you really do get the proper results.

SELECT MAX(replyID), topicID FROM table GROUP BY topicID LIMIT 10