3 Replies Latest reply on Mar 13, 2007 2:10 PM by muellertj

    query distinct with top date

    muellertj
      I am querying for distinct data from one table but I need only the most recent data. I am currently using the query included below, but if there are two entries for any of the paddocks and the species are different in them I will get both entries returned. I need it to return just the most recent of those two entries. How do I do this??

      If this were my data I would only want it to return: 1, cool and 2, warm

      Paddock Species Date
      1 Warm 1/1/05
      1 Cool 1/7/05
      2 1/5/05
      2 Warm 1/7/05

      <cfquery name="getpaddocks" datasource="dairy">
      SELECT this.paddock, this.species
      FROM
      (select distinct paddock, species
      FROM dairy.dbo.paddockdata
      WHERE FarmId='#session.data.farmid#'
      and year(datadate)= year(current_timestamp)
      )as this
      ORDER BY RIGHT('00000' + paddock, 3)
      </cfquery>
        • 1. Re: query distinct with top date
          Dan Bracuk Level 5
          something like this might work

          select p.paddock thepaddock, p.species thespecies
          from paddockdata p join
          (
          select paddock tpaddock, species tspecies, max(datadate) maxdate
          from paddockdata
          where FarmId='#session.data.farmid#'
          and year(datadate)= year(current_timestamp)
          group by paddock, species
          ) temp on paddock = tpaddock and species = tspecies and datadate = maxdate
          where FarmId='#session.data.farmid#'
          and year(datadate)= year(current_timestamp)
          • 2. Re: query distinct with top date
            muellertj Level 1
            I tried your suggestion but it gives me the same set of results. I just can't figure out how to get it to only output the most recent date./
            • 3. Re: query distinct with top date
              muellertj Level 1
              I tweaked what Dan gave me and finally go this to work:
              select p.paddock thepaddock, p.species thespecies
              from dairy.dbo.paddockdata p join
              (
              select distinct paddock tpaddock, max(datadate) maxdate
              from dairy.dbo.paddockdata
              where FarmId='#session.data.farmid#'
              and year(datadate)= year(current_timestamp)
              group by paddock
              ) temp
              on paddock = tpaddock
              and datadate = maxdate