Topic awaiting preservation: Simple MySQL query help.

I have the following table:

<BLOCKQUOTE><FONT face="Verdana, Arial">code:</font><HR><pre>mysql> SELECT id, thread_id, time FROM Earth_Msg;
+----+-----------+----------------+

hmm.. I'm a little unclear on the issue here.

This should return both id's, no?
Do you mean that they are being grouped by "id" instead of "thread_id"?




:[ Computers let you make more mistakes faster than any other invention in human history, with the possible exceptions of handguns and tequila. ]:

jiblet: I'll have another look when I've slept (I've been out all night trick and treating - oddly noone mch wanted to give a large man in a mask any sweets/candy!!). It might need something complex but it is also possible that if you did a descending (or is it ascending? I'd go with DESC off the top of my head) order by time it might spit out the results you want. No guarantees though.

Emps


You're my wife now Dave

jiblet, try SELECT * FROM tablename ORDER BY fieldname [DESC

jiblet: I'm not sure if it is completely relevant but I found this pretty useful when wandering into territory away from the 'standard' MySQL statements:
www.devshed.com/Server_Side/MySQL/MySQLWiz

Check it out I found the way it is written a little annoying but it was a useful piece.

Emps


You're my wife now Dave

Yes, that is a good article. I read it when it came out, but it might be a good re-read now that I've got more SQL experience. Okay. To clarify my question even further (I did find a solution btw, but if I can figure this out I can write a much quicker solution). To review, the standard query:

SELECT id, thread_id, time FROM Earth_Msg;

gives me this table (argh, why doesn't the code tag used a fixed width font?):

+----+-----------+----------------+

jiblte: Save it as a .txt file and post the link here.

Emps


You're my wife now Dave

http://www.coffman.umn.edu/earth/emboard.txt

still needs a lot of work before it will be widely useful.

-jiblet

> I want to select the MAX(time) for each thread_id. In other words, what query should
> I use to return rows 2, 4 and 15 from this table?

Then later:


> Okay, to refine my question. I tried the query:
>
> SELECT id, thread_id, MAX(time) FROM Earth_Msg GROUP BY
> thread_id;
>
> Which gives me the times I want, but unfortunately the ids
> that it returns are the first ids for each group, not the
> ids associated with the row that has the MAX(time). Why
> would it behave this way?

My guess is:

In your original query: you're querying on more than the fields from which you wanted the specific info. By that I mean: if all you want is the max(time) for each thread_id, then you (by your own definition) have to strictly select *only* the thread_id and the max(time) as it. So:

SELECT thread_id, max(time) as it
FROM Earth_Msg
order by...
limit...

etc.

You could do a union or a join to then derive individual message id's, etc. But a word of warning that in PHP this slows things down quite a bit on most servers I've run it.

Hope that is close anyway. And hi! I don't post here that much but hi!

ad



--
Because I can!