must be bad luck, but this method doesn't work for me. to go to my actual case, I'm currently developing on: I have a bulletin board, and want to find any forum's thread with the most recent posting.
here's what I tried first: mysql> select forum, id, lastpostdate from uni_threads group by forum having lastpostdate=max(lastpostdate); this results in: +-------+-----+--------------+ | forum | id | lastpostdate | +-------+-----+--------------+ | 31 | 281 | 1065732416 | | 67 | 487 | 1054135575 | +-------+-----+--------------+ 2 rows in set (0.00 sec) this is all records where the group function would have chosen the correct thread anyway. but I expected 33 rows for my 33 forums... then I tried the method from your URL: mysql> create temporary table tmp select id, max(lastpostdate) as lpd from uni_threads group by forum; Query OK, 33 rows affected (0.02 sec) Records: 33 Duplicates: 0 Warnings: 0 mysql> select t2.forum, t2.id from uni_threads as t2, tmp where t2.id=tmp.id and t2.lastpostdate=tmp.lpd; but it did the same, which I can't quite understand :( +-------+-----+ | forum | id | +-------+-----+ | 31 | 281 | | 67 | 487 | +-------+-----+ 2 rows in set (0.00 sec) from the notes on that page (Csaba Gabor on Sunday March 16 2003), I tried the following: SELECT t1.Forum, t1.ID FROM Threads AS t1 LEFT JOIN Threads AS t2 ON t1.Forum = t2.Forum AND t1.LastPostDate < t2.LastPostDate WHERE t2.LastPostDate IS NULL ORDER BY Forum; it correctly returned all my 33 rows, but it's _very_ slow because of its join, I believe: 33 rows in set (0.75 sec) anyway, I resolved my actual problem in another way someone might be interested... I first get the maximum of the last post date for any forum: <dates> := SELECT MAX(LastPostDate) FROM Threads GROUP BY Forum; then I believe there's exactly one (or none) post with this timestamp in the posts table: SELECT Posts.ID as PostID, Posts.Date, Posts.Thread, Posts.User, Posts.UserName, Threads.Forum, Threads.Subject FROM Threads LEFT JOIN Posts ON Threads.ID = Posts.Thread AND Posts.Date = Threads.LastPostDate WHERE Threads.LastPostDate IN (<dates>) ORDER BY Posts.Date DESC; so I won't directly select the information from the Threads table but from the Posts table and link this one back to the Threads... it may sound a bit confusing but it's much faster and does what I need. that's everything I do for now. this reduced my PHP function time from 120 to 45 msec and I believe this should be OK for now :) -- Yves Goergen [EMAIL PROTECTED] Please don't CC me (causes double mails) ----- Original Message ----- From: "Tobias Asplund" <[EMAIL PROTECTED]> To: "Yves Goergen" <[EMAIL PROTECTED]> Cc: "List: MySQL" <[EMAIL PROTECTED]> Sent: Friday, November 21, 2003 9:53 AM Subject: Re: How to 'customize' GROUP BY? > > I think that > http://www.mysql.com/doc/en/example-Maximum-column-group-row.html > covers your problem here. > > You either have to solve it with Temporary tables, the MAX-Concat trick > (in the url above) or a subquery (which will be more inefficient than the > other two options). > > > On Thu, 20 Nov 2003, Yves Goergen wrote: > > > Hi again... > > yet another question to this list that maybe someone can easily answer me... > > > > When I do a GROUP BY on a column of my query, I'll get one random row from > > the entire group. But how can I tell MySQL to, i.e., give me the row with > > the highest value of another column or so? I mean something like > > > > SELECT id, grp FROM tbl GROUP BY grp ORDER BY id > > > > but with the ORDER BY relating to the GROUP... I don't know how to express > > this in SQL since it doesn't seem to be possible? > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
