SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    Froot r gewd SubKamran's Avatar
    Join Date
    May 2002
    Location
    North Star State
    Posts
    597
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Advanced Query Help

    This is what I need to do:

    1. Get the records from the DB
    2. Do NOT get a record if "field A" is repeated.
    3. Grab the first record from that with the latest ID.

    Here's my table:
    Code:
    Table A
    ------------
    id (Primary Key, Auto Increment)
    session_id (VarChar)
    session_user_id (MediumInt)
    session_user_ip (VarChar)
    session_user_last_updated (VarChar)
    session_user_last_page (VarChar)
    When a new user comes, a record is added. Now, this could be the SAME user, his 2nd visit and his PREVIOUS record is still there. Well guess what? I'll need to display a list of active users, and I do NOT want to display REPEAT users (the key is their User ID and IP).

    I have done this successfully with the following query:
    Code:
    SELECT u.userid, u.username, u.userlevel, s.session_user_logged_in, s.session_user_ip, s.session_user_last_updated, s.session_user_last_page
    FROM users u, session s
    WHERE u.userid = s.session_user_id
    GROUP BY u.username, s.session_user_ip
    ;
    But now, I want to select the LATEST record (using the ID as reference). So I need to get the latest record, because this query only gets the FIRST record.

    Does anyone understand? It's hard to explain...

    See, I will be displaying this info in a table on a page, and I need to get the latest info from the user, not old info! No one wants to see their OLD info now do they?

    I tried using:

    Code:
    ORDER BY s.id DESC
    After the GROUP BY clause, but it didn't work...
    "Sometimes little is more."
    Kamran A
    Web Dev/Designer
    Keyboard not found: Please Press F1 to Continue

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    see this thread: http://www.sitepointforums.com/showt...hreadid=111452

    use the subquery if you're on 4.1

    rudy


Bookmarks

Posting Permissions

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