|Getting max date when joining a table to itself [message #903377]
||Thu, 23 August 2012 08:34
| Michael Harless
Registered: January 2012
I am an SQL rookie, so bear with me. I am attempting to get the max date for two different values within the same set of columns. I am joining the table to itself using aliases, but I don't have my joins right (I think), as I am getting multiple rows instead of the one that I was expecting. Here is my query and results:|
C1.CVLVAL as weight,
max (C1.CVLDATE) as entered,
C3.CVLVAL as height,
from CLTCVL as C1
left outer join CLTCVL as C2 on ( C2.CVLCAS = C1.CVLCAS )
( select CVLTYPE, CVLVAL, CVLCAS, max ( CVLDATE ) as heightdate
from CLTCVL where CVLTYPE = '4' group by CVLVAL, CVLTYPE, CVLCAS
) as C3
on C3.CVLCAS = C2.CVLCAS
where C1.CVLTYPE = '1'
and C1.CVLCAS = ?
group by C1.CVLVAL, C3.CVLVAL, C3.heightdate
order by C1.CVLVAL
WEIGHT WEIGHTDATE HEIGHT HEIGHTDATE
215.00 Aug 10, 2012 9:24 PM 66.75 Jul 18, 2012 8:56 AM
215.00 Aug 10, 2012 9:24 PM 67.25 Aug 10, 2012 9:24 PM
Any suggestions? Thanks.
Powered by FUDForum
. Page generated in 0.01619 seconds