Very Important Query

Well how can i get the last post username for each thread from this database :D

Might try


SELECT
MAX(threadID), username
FROM
users, threads
WHERE
threads.userID=users.userID
ORDER BY
threads.threadID


???

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.

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

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 :(

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....

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.

rows:
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?