MYSQL ORDER BY problem

[eelco]

Hi,

I have two fields 'mod_date' TIMESTAMP(14), and 'thread_ID' TINY_INT(10).

The values in mod_date are unique and the values in thread_ID can be the same.

I wan't to order the rows in the resultset by mod_date and thread_ID. Where thread_ID doesn't have to be in ASC or DESC order, but rows with equal values for thread_ID must be listed by eachother(form a group).
These 'groups' must be ordered by the most recent mod_date represented in it.

I tried to use the following queries:
SELECT thread_ID, UNIX_TIMESTAMP(mod_date) as mod_date, FROM table ORDER BY mod_date DESC, thread_ID;

SELECT thread_ID, UNIX_TIMESTAMP(mod_date) as mod_date, FROM table ORDER BY thread_ID, mod_date DESC;

I also tried to make the thread_ID field a CHAR and a BLOB field.

I realy hope somone can give a solution for this.

Thanks in advance.

[[deleted]]

Hi,

it's always good to give an example of the output wanted.

[eelco]

OK i will give an example with the timstamps field converted in normal date time format.

mod_date Thread_ID
22-01-2002 20:30:00 2
21-01-2002 09:31:15 2
17-01-2002 18:50:03 2
22-01-2002 17:15:01 1
22-01-2002 13:10:50 1
20-01-2002 22:08:10 1
17-01-2002 10:10:00 1
21-01-2002 03:05:30 3
21-01-2002 03:01:31 3

I hope somone can work with this.

Thanks in advance.