Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29

    Answered: MAX(date) not working

    I have a table which stores logs of every user.

    I want to retrieve a user latest log

    select * from user_logs where user_id=@userId having max(created_date);

  2. Best Answer
    Posted by gvee

    "Step 1: get the latest date per user
    Code:
    SELECT user_id
         , Max(created_date) As max_created_date
    FROM   user_logs
    GROUP
        BY user_id
    Step 2: join this back to the main table to get the latest full row for each record:
    Code:
    SELECT *
    FROM   user_logs
     INNER
      JOIN (
            SELECT user_id
                 , Max(created_date) As max_created_date
            FROM   user_logs
            GROUP
                BY user_id
           ) As latest_logs
        ON latest_logs.user_id = user_logs.user_id
       AND latest_logs.max_created_date = user_logs.created_date
    "


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Max() doesn't work that way. You probably want to use:
    Code:
    SELECT *
       FROM user_logs
       WHERE  @userId = user_id
       ORDER BY created_date DESC
       LIMIT 1;
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29
    Quote Originally Posted by Pat Phelan View Post
    Max() doesn't work that way. You probably want to use:
    Code:
    SELECT *
       FROM user_logs
       WHERE  @userId = user_id
       ORDER BY created_date DESC
       LIMIT 1;
    -PatP
    can you just help me in for all users with latest date

  5. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Step 1: get the latest date per user
    Code:
    SELECT user_id
         , Max(created_date) As max_created_date
    FROM   user_logs
    GROUP
        BY user_id
    Step 2: join this back to the main table to get the latest full row for each record:
    Code:
    SELECT *
    FROM   user_logs
     INNER
      JOIN (
            SELECT user_id
                 , Max(created_date) As max_created_date
            FROM   user_logs
            GROUP
                BY user_id
           ) As latest_logs
        ON latest_logs.user_id = user_logs.user_id
       AND latest_logs.max_created_date = user_logs.created_date
    George
    Home | Blog

  6. #5
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29
    Quote Originally Posted by gvee View Post
    Step 1: get the latest date per user
    Code:
    SELECT user_id
         , Max(created_date) As max_created_date
    FROM   user_logs
    GROUP
        BY user_id
    Step 2: join this back to the main table to get the latest full row for each record:
    Code:
    SELECT *
    FROM   user_logs
     INNER
      JOIN (
            SELECT user_id
                 , Max(created_date) As max_created_date
            FROM   user_logs
            GROUP
                BY user_id
           ) As latest_logs
        ON latest_logs.user_id = user_logs.user_id
       AND latest_logs.max_created_date = user_logs.created_date

    That works like champ thanks for making my job simple

Posting Permissions

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