Posted by MultiTaskinG on 06/14/05 14:17

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 0158c01d508b7f6b439218b37b95c9d8 1118615171 0
Edit Delete 16 08852417c9150755dc79a227bdc14166 1118614496 0
Edit Delete 16 1f36e5e49a9f63c155da65d6b50cdf57 1118700152 0
Edit Delete 16 21a893b9e131f93fd74087aecd6e0ad9 1118615378 0
Edit Delete 16 23a08379da46d6ca93688bfd552a9fd2 1118616732 0
Edit Delete 16 26ddd774e9ed5027e2b63ee76e07aa63 1118614810 0
Edit Delete 16 2b8d2bc3a4921133f4f66550ff1e0b62 1118615722 0
Edit Delete 16 4ffac6f937c627710df83286427d5e77 1118700068 0
Edit Delete 16 5599e5171abe3ff1db3a1de96dd622df 1118700205 0
Edit Delete 16 5abbc47f16ae3d5e999d083357f67226 1118617178 0
Edit Delete 16 683d385ec0d315b34be5fab64b64cd4a 1118700174 0
Edit Delete 16 97139b90f82c9d54df4c727ca59dfee8 1118614046 0
Edit Delete 16 af32d2171a51c3635513f77ecff3a7cd 1118615830 0
Edit Delete 16 e0ab7e66c7dcb4e50505d7b143f9bccf 1118613704 0
Edit Delete 16 e0ab7e66c7dcb4e50505d7b143f9bccf 1118613714 1
Edit Delete 16 e0ab7e66c7dcb4e50505d7b143f9bccf 1118613734 2
Edit Delete 16 e0ab7e66c7dcb4e50505d7b143f9bccf 1118613744 1
Edit Delete 16 e10d9627b13b9e984dd8d3831cfc7c10 1118615545 0
Edit Delete 16 h599e5171abe3ff1db3a1de96dd622df 1118613716 0


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


so I have ONE UNIQUE THREAD ( e0ab7e66c7dcb4e50505d7b143f9bccf ) 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 e0ab7e66c7dcb4e50505d7b143f9bccf 1118613704 0
Edit Delete 16 e0ab7e66c7dcb4e50505d7b143f9bccf 1118613714 1
Edit Delete 16 h599e5171abe3ff1db3a1de96dd622df 1118613716 0
Edit Delete 16 e0ab7e66c7dcb4e50505d7b143f9bccf 1118613734 2
Edit Delete 16 e0ab7e66c7dcb4e50505d7b143f9bccf 1118613744 1
Edit Delete 16 97139b90f82c9d54df4c727ca59dfee8 1118614046 0
Edit Delete 16 08852417c9150755dc79a227bdc14166 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 !

• Next in forum: Database "pruning" tool?
• Prev in forum: Reordering Columns
• Thread view: mysql 4.0.x - ORDER BY ? GROUP BY ? please help me for "hierarchical" query

[Reply to this message]