Thread: Select max

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    1
    Rep Power
    0

    Select max


    Hello all.

    I have 2 tables,
    - Users with userId, userName
    - Affiliations with id, affiliationId, affiliationStartDate, affiliationEndDate

    How to find in a select all guys that haven't renew their affiliation before a certain date ?
    So I'm looking for userName and max(affiliationEndDate) where max(affiliationEndDate) < '2012-11-01' ?

    I'm using MySql 3.23.58

    Yhanks for your help
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Something like this (not verified):
    Code:
    SELECT
      u.userName
      ,max(a.affiliationEndDate)
    FROM
      users u
    INNER
      JOIN
        affiliations a
        ON u.userid = a.id
    GROUP
      BY
        u.userName
    HAVING
      max(a.affiliationEndDate) < '2012-11-01'
    Originally Posted by ParisByNight
    I'm using MySql 3.23.58
    Holy cr*p!
    If you still use a software from 2003 I wonder how old your car is. Or if you work for NASA and have a really long procurement time.
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo