query distinct with top date

[Tonyam]

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>

[Dan_Bracuk]

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)

[Tonyam]

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./

[Tonyam · Accepted solution]

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