Closed Thread Icon

Topic awaiting preservation: Simple MySQL query help. Pages that link to <a href="https://ozoneasylum.com/backlink?for=11923" title="Pages that link to Topic awaiting preservation: Simple MySQL query help." rel="nofollow" >Topic awaiting preservation: Simple MySQL query help.\

 
Author Thread
jiblet
Paranoid (IV) Inmate

From: Minneapolis, MN, USA
Insane since: May 2000

posted posted 10-31-2001 21:19

I have the following table:

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

bitdamaged
Maniac (V) Mad Scientist

From: 100101010011 <-- right about here
Insane since: Mar 2000

posted posted 11-01-2001 02:33

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. ]:

Emperor
Maniac (V) Mad Scientist with Finglongers

From: Cell 53, East Wing
Insane since: Jul 2001

posted posted 11-01-2001 02:53

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

lallous
Paranoid (IV) Inmate

From: Lebanon
Insane since: May 2001

posted posted 11-01-2001 08:01

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

Emperor
Maniac (V) Mad Scientist with Finglongers

From: Cell 53, East Wing
Insane since: Jul 2001

posted posted 11-01-2001 14:08

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

jiblet
Paranoid (IV) Inmate

From: Minneapolis, MN, USA
Insane since: May 2000

posted posted 11-01-2001 17:42

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?):

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

Emperor
Maniac (V) Mad Scientist with Finglongers

From: Cell 53, East Wing
Insane since: Jul 2001

posted posted 11-01-2001 18:07

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

Emps


You're my wife now Dave

jiblet
Paranoid (IV) Inmate

From: Minneapolis, MN, USA
Insane since: May 2000

posted posted 11-01-2001 19:08

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

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

-jiblet

AdamD1
Nervous Wreck (II) Inmate

From: Toronto, ON, Canada
Insane since: Jul 2001

posted posted 11-02-2001 20:16

> 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!

« BackwardsOnwards »

Show Forum Drop Down Menu