Darran Kartaschew wrote:
You need to do this in 2 steps.Warning: SQL newbie... I'm trying to create a query where the most recent entry for each user is returned from a forum table, and sorted by username. The fields are simply: user_id, username, post, last_updated. No primary key defined.I've tried the simple "SELECT * FROM posts GROUP BY user_id DESC ORDER BY username". It sorta returns what I'm after, but not the latest post from each user, (but the first post). Now my understanding of the GROUP BY function may not be correct, but according to the MySQL manual adding DESC after GROUP BY should return what I'm after but doesn't appear to do so. Any hints? PS. Table type is InnoDB, running on MySQL 4.0.16-max-nt. Darran Step 1: select user_id, max(last_updated) as max_last_updated from posts group by user_id You probably need to do this into a temporary table or something. Check the docs for more details, but you can do something like: create temporary table tmp_latest_post_by_user ( select ........... ) to create temporary tables ( fill in the ......... ) Step 2: select * from posts inner join tmp_latest_post_by_user on posts.user_id=tmp_latest_post_by_user and posts.last_updated=tmp_latest_post_by_user.max_last_updated In this step you join the main table with the temporary table of latest posts by user, getting only the rows that match from both tables ( inner join ). Dan --- By the way, I had a look at your company's web site ( looks pretty nice ) and thought that since you're in the Natural Health business, you have a little more to loose than most Australians over the Free Trade Agreement. If you don't want the largest of the US drug manufacturers making things difficult for you ( copyright & patent law ), I suggest opposing the FTA. See http://yro.slashdot.org/article.pl?sid=04/03/05/229228&mode=thread for the latest Slashdot story on it... Sorry about the political rant, but this stuff is important and we don't have much time. --
IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au |
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
