Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2012
    Posts
    19

    Unanswered: Display columns based on MAX() of other column

    I am trying to display information based on a MAX(date) function and a GROUP BY function.

    However despite being able to display the correct max date the other columns I want to select do not relate the the max date record.

    For example (date btw is dd/mm/yyyy)

    Table A
    name | Date | Notes
    ----------------------------
    John | 10/01/2014 | This is the wrong note
    John | 10/02/2014 | Note B
    Lisa | 10/01/2014 | This is the wrong note
    Lisa | 10/02/2014 | Note B

    When I write
    Code:
    SELECT A.name, MAX(A.Date), A.Notes
    FROM A
    GROUP BY A.name
    My output is
    name | Date | Notes
    ----------------------------
    John | 10/02/2014 | This is the wrong note
    Lisa | 10/02/2014 | This is the wrong note

    Rather than
    name | Date | Notes
    ----------------------------
    John | 10/02/2014 | Note B
    Lisa | 10/02/2014 | Note B

    Can anyone help me on this?

    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You join your grouped query back to the original table, like so:
    Code:
    SELECT a.*
    FROM   a
     INNER
      JOIN (
            SELECT name
                 , Max(date) As max_date
            FROM   a
            GROUP
                BY name
           ) As a_subquery
        ON a_subquery.name = a.name
       AND a_subquery.max_date = a.date
    George
    Home | Blog

Posting Permissions

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