Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    9

    Red face Unanswered: unique rows based on the newest date for each member (was "PLSE, help with Query")

    I need a query that would give me the unique rows base on the newest date for each member.

    Table layout:

    idAttendance idMember AttendDate AttendStatus
    1 8 10/01/1999 0
    2 9 01/01/2000 0
    3 10 30/06/2004 0
    4 11 15/02/2004 0
    5 12 18/03/2005 0
    6 9 04/04/2006 1


    I would like to see the result:

    idAttendance idMember AttendDate AttendStatus
    1 8 10/01/1999 0
    3 10 30/06/2004 0
    4 11 15/02/2004 0
    5 12 18/03/2005 0
    6 9 04/04/2006 1

    because record number 6 for member 9 is newer than record number 2.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select idAttendance 
         , idMember 
         , AttendDate 
         , AttendStatus
      from daTable as T
     where AttendDate
         = ( select max(AttendDate)
               from daTable
              where idAttendance = T.idAttendance )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    9

    Talking THANKS.... small adjustment

    Thanks!!!!!!!!!!!!!!!!!!!!!!!!!

    Just replace "where idAttendance = T.idAttendance"
    with "where idMember = T.idMember"
    and that is it.3

    Quote Originally Posted by r937
    Code:
    select idAttendance 
         , idMember 
         , AttendDate 
         , AttendStatus
      from daTable as T
     where AttendDate
         = ( select max(AttendDate)
               from daTable
              where idAttendance = T.idAttendance )

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    good job fixing my error, i think you understand sql now
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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