Jump to content

query


xbl1
 Share

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...