SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Showing their firstPayment and latestPayment on the same row

    This query works when I include the "limit 1", but the data is not correct. I'm trying to show their firstPayment and their latestPayment for those people who upgraded their membership this time last year. Am I getting close?

    Thanks!


    Code:
    select date_format(payDate, '%m-%d-%Y') as firstPayment, u.uID, concat(u.firstname,' ', u.lastname) AS name, 
    u.subscription, (select max(payDate) from payments group by uID limit 1) as latestPayment
    from payments p
    inner join users u
    on p.uID = u.uID
    where frequency = 'Yearly'
    and payDate like '2011-05%'
    order by firstPayment;
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    How about (didn't test it):
    Code:
    select 
        date_format(min(payDate), '%m-%d-%Y') as firstPayment
      , u.uID
      , concat(u.firstname,' ', u.lastname) AS name
      , u.subscription
      , max(payDate) as latestPayment
    from payments p
    inner join users u
    on p.uID = u.uID
    where frequency = 'Yearly'
    GROUP BY u.uID
    HAVING min(payDate) like '2011-05%'
    order by firstPayment;
    I actually don't know if you can do a LIKE on a date like that? But if it worked in your query, I guess it'll work in this one too

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    That worked perfectly. Thank you!
    Convert your dollars into silver coins. www.convert2silver.com


Bookmarks

Posting Permissions

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