mysql, ORDER BY ? GROUP BY ? please help me for a "hierarchical"q uery

[MultiTaskinG]

I want to retrieve all comment stored from my web users ordered BY
THREAD and BY TIMESTAMP (INT 11) with a single query (if is possible)
now I launch this query:
SELECT thread, timestamp, level FROM comments WHERE articleid=16 ORDER
BY thread, timestamp,level

the result is
--------------------------

articleid

thread

timestamp

level
Edit Delete 16 0158c01d508b7f6 b439218b37b95c9 d8
1118615171 0
Edit Delete 16 08852417c915075 5dc79a227bdc141 66
1118614496 0
Edit Delete 16 1f36e5e49a9f63c 155da65d6b50cdf 57
1118700152 0
Edit Delete 16 21a893b9e131f93 fd74087aecd6e0a d9
1118615378 0
Edit Delete 16 23a08379da46d6c a93688bfd552a9f d2
1118616732 0
Edit Delete 16 26ddd774e9ed502 7e2b63ee76e07aa 63
1118614810 0
Edit Delete 16 2b8d2bc3a492113 3f4f66550ff1e0b 62
1118615722 0
Edit Delete 16 4ffac6f937c6277 10df83286427d5e 77
1118700068 0
Edit Delete 16 5599e5171abe3ff 1db3a1de96dd622 df
1118700205 0
Edit Delete 16 5abbc47f16ae3d5 e999d083357f672 26
1118617178 0
Edit Delete 16 683d385ec0d315b 34be5fab64b64cd 4a
1118700174 0
Edit Delete 16 97139b90f82c9d5 4df4c727ca59dfe e8
1118614046 0
Edit Delete 16 af32d2171a51c36 35513f77ecff3a7 cd
1118615830 0
Edit Delete 16 e0ab7e66c7dcb4e 50505d7b143f9bc cf
1118613704 0
Edit Delete 16 e0ab7e66c7dcb4e 50505d7b143f9bc cf
1118613714 1
Edit Delete 16 e0ab7e66c7dcb4e 50505d7b143f9bc cf
1118613734 2
Edit Delete 16 e0ab7e66c7dcb4e 50505d7b143f9bc cf
1118613744 1
Edit Delete 16 e10d9627b13b9e9 84dd8d3831cfc7c 10
1118615545 0
Edit Delete 16 h599e5171abe3ff 1db3a1de96dd622 df
1118613716 0
----------------------------
so I have ONE UNIQUE THREAD ( e0ab7e66c7dcb4e 50505d7b143f9bc cf ) but
these result are't ordered by timestamp !!!!
are apparently ordered only by Thread (alfabethically ).

there is the possibility to get the result ordered by TIMESTAMP and
aggregate the data by thread ?


if I make this:

SELECT thread, timestamp, level FROM comments WHERE articleid=16 ORDER
BY timestamp, thread, level
I have the result ordered by timestamp but not ordered by thread ( see
the third line )

--------------------------------
articleid

thread

timestamp

level
Edit Delete 16 e0ab7e66c7dcb4e 50505d7b143f9bc cf
1118613704 0
Edit Delete 16 e0ab7e66c7dcb4e 50505d7b143f9bc cf
1118613714 1
Edit Delete 16 h599e5171abe3ff 1db3a1de96dd622 df
1118613716 0
Edit Delete 16 e0ab7e66c7dcb4e 50505d7b143f9bc cf
1118613734 2
Edit Delete 16 e0ab7e66c7dcb4e 50505d7b143f9bc cf
1118613744 1
Edit Delete 16 97139b90f82c9d5 4df4c727ca59dfe e8
1118614046 0
Edit Delete 16 08852417c915075 5dc79a227bdc141 66
1118614496 0
-----------------------


i Have mysql version 4.0.x
so I haven't subquery and WITH ROLLUP OPTION too :-(


Can you help me ?
thanks a lot in advance !

[Bill Karwin]

MultiTaskinG wrote:

there is the possibility to get the result ordered by TIMESTAMP and
aggregate the data by thread ?

I think you need to track which comment is "root" of each thread, and
then map each comment to its thread's root, JOIN to it in your query so
you can get the timestamp of the start of the thread.

To do this you could use two new columns in your table: one to uniquely
identify the comment (a primary key for the table), and another to
reference the root comment for the thread.

SELECT c.thread, c.timestamp, c.level
FROM comments AS c INNER JOIN comments AS root
ON c.thread_root = root.pk
WHERE c.articleid=16
ORDER BY root.timestamp, c.level, c.timestamp

Another way would be to add one new field, to mark a given comment as
the root of its thread. That might be easier; just set `is_root` to 1
if someone is posting a new message, or 0 if they're posting in reply to
another comment.

SELECT c.thread, c.timestamp, c.level
FROM comments AS c INNER JOIN comments AS root
ON c.thread = root.thread and root.is_root = 1
WHERE c.articleid=16
ORDER BY root.timestamp, c.level, c.timestamp

Regards,
Bill K.