MySQL Server Forum - mysql, ORDER BY ? GROUP BY ? please help me for a "hierarchical"

Author

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

MultiTaskinG

2005-06-14, 7:23 am

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 0158c01d508b7f6b4392
18b37b95c9d8
1118615171 0
Edit Delete 16 08852417c9150755dc79
a227bdc14166
1118614496 0
Edit Delete 16 1f36e5e49a9f63c155da
65d6b50cdf57
1118700152 0
Edit Delete 16 21a893b9e131f93fd740
87aecd6e0ad9
1118615378 0
Edit Delete 16 23a08379da46d6ca9368
8bfd552a9fd2
1118616732 0
Edit Delete 16 26ddd774e9ed5027e2b6
3ee76e07aa63
1118614810 0
Edit Delete 16 2b8d2bc3a4921133f4f6
6550ff1e0b62
1118615722 0
Edit Delete 16 4ffac6f937c627710df8
3286427d5e77
1118700068 0
Edit Delete 16 5599e5171abe3ff1db3a
1de96dd622df
1118700205 0
Edit Delete 16 5abbc47f16ae3d5e999d
083357f67226
1118617178 0
Edit Delete 16 683d385ec0d315b34be5
fab64b64cd4a
1118700174 0
Edit Delete 16 97139b90f82c9d54df4c
727ca59dfee8
1118614046 0
Edit Delete 16 af32d2171a51c3635513
f77ecff3a7cd
1118615830 0
Edit Delete 16 e0ab7e66c7dcb4e50505
d7b143f9bccf
1118613704 0
Edit Delete 16 e0ab7e66c7dcb4e50505
d7b143f9bccf
1118613714 1
Edit Delete 16 e0ab7e66c7dcb4e50505
d7b143f9bccf
1118613734 2
Edit Delete 16 e0ab7e66c7dcb4e50505
d7b143f9bccf
1118613744 1
Edit Delete 16 e10d9627b13b9e984dd8
d3831cfc7c10
1118615545 0
Edit Delete 16 h599e5171abe3ff1db3a
1de96dd622df
1118613716 0

----------------------------

so I have ONE UNIQUE THREAD ( e0ab7e66c7dcb4e50505
d7b143f9bccf ) 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 e0ab7e66c7dcb4e50505
d7b143f9bccf
1118613704 0
Edit Delete 16 e0ab7e66c7dcb4e50505
d7b143f9bccf
1118613714 1
Edit Delete 16 h599e5171abe3ff1db3a
1de96dd622df
1118613716 0
Edit Delete 16 e0ab7e66c7dcb4e50505
d7b143f9bccf
1118613734 2
Edit Delete 16 e0ab7e66c7dcb4e50505
d7b143f9bccf
1118613744 1
Edit Delete 16 97139b90f82c9d54df4c
727ca59dfee8
1118614046 0
Edit Delete 16 08852417c9150755dc79
a227bdc14166
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

2005-06-14, 1:23 pm

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.

Sponsored Links