Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Location
    Calgary
    Posts
    1

    Unanswered: Selecting max date for each site

    I'm having trouble building a query

    I need to develop a query that will find:
    the last record for each site in which zcal is not null

    my table trend has fields tsite,tdate, tzcal

    The table contains data for multiple sites and the date the record was entered and the value associated to tzcal.

    I've tried

    SELECT tsite, MAX(tdate) as maxtdate, tddate, tzcal
    from trend
    where tzcal is not null
    GROUP By tsite

    My results com back with maxtddate being the last entry for each tsite however tddate and tzcal values are from the first record for each tsite value

    How do I change to get the results I want?

    Thank you in advance.

    todd

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    Re: Selecting max date for each site

    Originally posted by todd.benko
    My results com back with maxtddate being the last entry for each tsite however tddate and tzcal values are from the first record for each tsite value
    this is because you are grouping incorrectly

    see 13.9.3 GROUP BY with Hidden Fields for a detailed explanation

    you will not be able to do it with a simple GROUP BY

    the straight-forward way to do it is with a correlated subquery

    PHP Code:
    select tsite
         
    tdate
         
    tddate
         
    tzcal
      from trend zzz
     where tzcal is not null
       
    and tdate =
           ( 
    select max(tdate)
               
    from trend
              where tsite 
    zzz.tsite 
    unfortunately, if you're not on mysql 4.1, you can't use any subqueries, correlated or otherwise

    so this is where you have to re-write it as a self-join

    you basically join each row in a tsite to every other row in the same tsite

    just like the correlated subquery does, eh

    PHP Code:
    select t1.tsite
         
    t1.tdate
         
    t1.tddate
         
    t1.tzcal
      from trend t1
    inner
      join trend t2  
        on t1
    .tsite t2.tsite
     where t1
    .tzcal is not null
    group
        by t1
    .tsite
         
    t1.tdate
         
    t1.tddate
         
    t1.tzcal
    having t1
    .tdate max(t2.tdate
    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
  •