Hi i have a table 'forum' with

[Ewoud]

Hi

i have a table 'forum' with
id - topic - user - datum - comment

What i want is a table with only the max date with the corresponding user grouped by the topics
so i can set the last message was post on ... by ...

i found the solution in SQL:
SELECT topic, user, datum
FROM forum t1
WHERE datum = (
SELECT MAX( datum )
FROM forum
WHERE topic = t1.topic
)
But this give a Syntax error in MySQL

Can someone convert it?
Or does anyone know the solution?

Thx,

 

[r937]

seems like you finally found the right forum

sorry to say this, but since your version of mysql does not support subqueries, you have to do this in two steps with a temp table

see

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

rudy

 

[Ewoud]

Isn't that realy slow?

 

[r937]

depends how you measure "really slow"

there doesn't seem to be an alternative, does there? i mean, short of upgrading to 4.1

rudy

 

[swampBoogie]

>>Isn't that realy slow?

Sure can be. Depends on how much data you have. As there is no other solution when using Mysql you don't have much choice. (Except using another DBMS that supports basic SQL. For instance postgreSQL which is free and superior to Mysql in almost every aspect.)

 

[Ewoud]

Yeaah i know but my hosting won't upgrade..
maybe i should run my own server...

Thx,