Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Unanswered: Easy one! MAX and GROUP BY returns too many rows

    I want to pull an ID, a latest date and a user ID from a table (all in same table). This works great for the 1st 2 pieces of data:

    SELECT ACTIVITY.COMPANY_ID, Max(ACTIVITY.UPDATED_DATE)
    FROM ACTIVITY
    GROUP BY ACTIVITY.COMPANY_ID;

    but when I add the last piece, I get lots of extra rows:

    SELECT ACTIVITY.COMPANY_ID, Max(ACTIVITY.UPDATED_DATE) AS MaxOfUPDATED_DATE, ACTIVITY.USER_ID
    FROM ACTIVITY
    GROUP BY ACTIVITY.COMPANY_ID, ACTIVITY.USER_ID;

    I tried to join the first query to the original table again and this returns too many rows too:

    SELECT ACTIVITY.COMPANY_ID, Max(ACTIVITY.UPDATED_DATE), ACTIVITY.USER_ID
    FROM ACTIVITY
    GROUP BY ACTIVITY.COMPANY_ID, ACTIVITY.USER_ID;

    Is it my grouping? Help! Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ricky2525
    I want to pull an ID, a latest date and a user ID ...
    you didn't say which user, so how about the lowest one
    Code:
    SELECT company_id
         , MAX(updated_date) AS latest_date
         , MIN(user_id) AS lowest_user
      FROM activity
    GROUP 
        BY company_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2009
    Posts
    4

    Thanks R!

    That works and will do in a pinch, but seems that once I have the latest record for the individual Company, there ought to be some way to select the exact user that is associated with that record... Ideas? Thanks again for your help.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ricky2525
    That works and will do in a pinch, but seems that once I have the latest record for the individual Company, there ought to be some way to select the exact user that is associated with that record... Ideas?
    ideas? yeah, i got ideas

    (tip: next time, please be explicit in your requirements)
    Code:
    SELECT a.company_id
         , a.updated_date
         , a.user_id 
      FROM ( SELECT company_id
                  , MAX(updated_date) AS latest_date
               FROM activity
             GROUP 
                 BY company_id ) AS m
    INNER
      JOIN activity AS a
        ON a.company_id   = m.company_id
       AND a.updated_date = m.latest_date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2009
    Posts
    4
    That's it!
    Sorry I wasn't clearer about what I needed.

    Seems they have a number of records for one Company with identical datestamps and different users so I added the MIN(User_ID) back in and it's perfect.

    Thanks a bunch for your time!

Posting Permissions

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