query

[xbl1]

Hi; i'd like to ask you how to write a query to get the final result same as table3 from Link.But please write in mysqlmy question:i got a table which is SubThread. i want to get everything relative to userName of kam.But if the threadId equal each other, i just want to select the row which is the biggest value of the postTime.and also if the threadId not equal each other, then the row with biggest value of the Postime, should be selected first

[Synook]

i got a table which is SubThread. i want to get everything relative to userName of kam.But if the threadId equal each other, i just want to select the row which is the biggest value of the postTime.and also if the threadId not equal each other, then the row with biggest value of the Postime, should be selected first

How about

SELECT * FROM SubThread WHERE userName = 'kam' GROUP BY threadId ORDER BY postTime DESC

[xbl1]

How about

SELECT * FROM SubThread WHERE userName = 'kam' GROUP BY threadId ORDER BY postTime DESC

1) if the threadId equal each other, i just select the row with biggest values of posttime2) if the threadId not equal each other, then the row with the biggest value of the posttime, be selected first.your query satisfy the 2), but it does not satify the 1). please check the table 4 which the result from your query, and comepair with the result of the table 3. Click Linkyou can see the threadId with the 129, the postTime with the id=24 is bigger than the postTime with the 10.It still does not pick up the row with the biggest value of the postTime from all the row with threadId 129.

[justsomeguy]

SELECT ThreadID, UserName, MAX(PostTime) AS PostTime FROM (SELECT Id AS ThreadID, UserName, PostTime FROM Thread UNION SELECT ThreadID, UserName, PostTime FROM SubThread) GROUP BY ThreadID ORDER BY PostTime DESC

[xbl1]

SELECT ThreadID, UserName, MAX(PostTime) AS PostTime FROM (SELECT Id AS ThreadID, UserName, PostTime FROM Thread UNION SELECT ThreadID, UserName, PostTime FROM SubThread) GROUP BY ThreadID ORDER BY PostTime DESC

Thanks a lot, it works fine now.