group by topic and order by the last modify time

joon

Let's suppose 4 fields.

The name of the first field is "number' . and it is primary key.
The name of the second field is "topic".
The name of the third field is " title".
The name of the last field is "modify_time".

I like to make a list which is grouped by topic and ordered by the last modify time.

Let's look at the below code.

select min(number) as min_number, title, max(modify_time) as last_time.
group by topic
order by last_time

The outputing of the above code has a problem which is that the list of title is not matched to the list of min_number.

Although I hoped that each title is matched to each min_number in the list, it is unexpectedly matched to each last_timet.

Would you help me?

Lars_Berg

The value of title is arbitrary within each group of topics, it is not connected to anything else.

http://www.mysql.com/doc/en/example-Maximum-column-group-row.html

The implementation of group by in Mysql (in non ANSI-mode) is really bad.

joon

Yes, it is arbitrary.

If it is always wrong, I can find what's problem.
If it is arbitrary, I am being fluctuated.
It was really really suffering.

By the way, is there no solution?
Can't you make it non_arbitrary, and connect to ID?

Do I have to back to Access?
or
Do I have to forward to Oracle?

LordShryku

What version are you using anyway? May be able to sqeak by with a sub-query

And the step forward would be Postgres. Cheaper than Oracle(read: Free), and more powerful than MySQL IMHO

joon

it's MySQL 4.0.15.(I set up with Automatical APM_setup)

Is there(in Postgres) any management tool like phpMyAdmin in mySQL?

Anyway I'll consider Postgres, but I like to solve this problem in mySQL at the moment

LordShryku

Well, same thing, jus phpPgAdmin. I just noticed no FROM statement in your query, nor a distinct clause. That would be the first shot I'd be trying..

SELECT   DISTINCT topic,
         min(number) as min_number, 
         title, 
         max(modify_time) as last_time
FROM     tableName
GROUP BY topic
ORDER BY last_time

joon

Omitting "FROM" was my careless.
Please forgive me.

I applied your code , but I found that connection to which record in a group is still arbirary

LordShryku

Maybe I'm a bit confused. You're looking for the min(number) or the max(modify_time)? If you only want one, you're probably going to have to drop one. Maybe this is a multiple query thing...

Sorry I couldn't be more help

joon

Yes, If I give up "order by last_time", I can connect "title" to "ID".

But I have to solve two problems (connecting to ID and order by last_time) at a time.

Anyway, very thanks to you.
If you happen to get an Idea on this issue, please leave a message later.

If you didn't say about the arbitrary, I might not notice that still.
and My feeling would be fluctuated by the arbitrary.

Thanks again.

See you, someday at somewhere....^.