View Full Version : Very Important Query

Oct 20th, 2005, 07:09 PM
Well how can i get the last post username for each thread from this database :D

Oct 20th, 2005, 07:56 PM
Might try

MAX(threadID), username
users, threads


Oct 21st, 2005, 03:35 AM
well thanx alot, but i guess you didnt get me, what i want is to get the last post for each thread, not the last thread.

Oct 21st, 2005, 09:52 AM
SELECT MAX(Posts.DatePosted) As LastPostDate, Posts.ThreadID
FROM Posts
GROUP BY Posts.ThreadID
ORDER BY Posts.DatePosted Desc
This will give you the the last post date for each thread, together with the threadid.

If you need more information, fi the username, you have to join the users table to the posts table

SELECT MAX(Posts.DatePosted), Posts.ThreadID, Users.Username As LastPostDate
FROM Posts INNER JOIN Users ON Posts.UserID = Users.UserID
GROUP BY Posts.ThreadID
ORDER BY Posts.DatePosted Desc

You can also join the Threads table to retrieve the threadtitle the same way as i did the users table to get the username

Oct 21st, 2005, 03:03 PM
there are two problems, one when i executed this query it gave me error that Usename in invalid coz its not added to GROUP BY, so i added it to the GROUP BY, when i executed it, it gave me all the usersnames for all threads :(

Oct 24th, 2005, 12:17 PM
can you mail met the database (access mdb file or sql server backup file) then i can build your query for you. I don't have the time to build the db myself and fill it with sample data. But building a query won't cost so much time, so....

Jun 18th, 2006, 12:01 PM
one more question at that topic - that combination of GROUP BY and ORDER BY works wrong at my case...

if i group by field `gid`, than order clause orderes ONLY grouped rows, not entire.

gid=1,views=2, data...
gid=1,views=3, data...
gid=2,views=4, data...
gid=2,views=56, data...

select *, MAX(views) AS mas_view FROM table WHERE 1 GROUP BY gid, ORDER BY views DESC

okej, MAX selects maximum of views, but other data belong to the first column (for gid=1) and third(for gid=2)

any help here?