SQL query help , MySQL 4.1

[Alexei Vladychevski]

i have 2 tables
forumMsg forumTopic
id idTopic Text idUser Views id Topic
TopOrder
-------------------------------------- ----------------------
1 1 Hello 1 4 1 General 2
2 1 More 2 1 2 Other 1
3 2 Help 3 4
4 2 Msg.. 4 5

i need a query to see forum topics and :
who was the las user that posted and sum(Views),
ordered by TopOrder
like this:

Topic Views LastUserId
-------------------------------------
Other 9 4
General 5 2

it is for MySQL ver 4.1, anyway if somebody can tell me how to make
the query in ANSI SQL, thanks...
Alexei.

[Bill Karwin]

Alexei Vladychevski wrote:

i have 2 tables
forumMsg forumTopic
id idTopic Text idUser Views id Topic
TopOrder
-------------------------------------- ----------------------
1 1 Hello 1 4 1 General 2
2 1 More 2 1 2 Other 1
3 2 Help 3 4
4 2 Msg.. 4 5

i need a query to see forum topics and :
who was the las user that posted and sum(Views),
ordered by TopOrder
like this:

Topic Views LastUserId
-------------------------------------
Other 9 4
General 5 2

it is for MySQL ver 4.1, anyway if somebody can tell me how to make
the query in ANSI SQL, thanks...

This is not tested, just a stab at the problem:

SELECT T.Topic, SUM(M1.Views) AS Views,
(SELECT M2.idUser FROM forumMsg M2 WHERE M2.id =
(SELECT MAX(M3.id) FROM forumMsg M3 WHERE M3.idTopic = T.id))
AS LastUserId
FROM forumTopic T INNER JOIN forumMsg M1 ON M1.idTopic = T.id
GROUP BY T.id
ORDER BY T.TopOrder

Regards,
Bill K.