Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder Cipher's Avatar
    Join Date
    Dec 2004
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Very Important Query

    Well how can i get the last post username for each thread from this database
    Attached Thumbnails Attached Thumbnails Very Important Query-dbschema.jpg  
    i need a creative atmosphere

  • #2
    Mega-ultimate member
    Join Date
    Jun 2002
    Location
    Winona, MN - The land of 10,000 lakes
    Posts
    1,855
    Thanks
    1
    Thanked 45 Times in 42 Posts
    Might try

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

  • #3
    Regular Coder Cipher's Avatar
    Join Date
    Dec 2004
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    i need a creative atmosphere

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    Code:
    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

    Code:
    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
    I am the luckiest man in the world

  • #5
    Regular Coder Cipher's Avatar
    Join Date
    Dec 2004
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    i need a creative atmosphere

  • #6
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    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....
    I am the luckiest man in the world

  • #7
    Regular Coder
    Join Date
    Aug 2004
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •