| Getting max date when joining a table to itself [message #903377] |
Thu, 23 August 2012 12:34  |
 Michael Harless
Messages: 14
Registered: January 2012 |
Junior Member |
|
|
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:
select
C1.CVLVAL as weight,
max (C1.CVLDATE) as entered,
C3.CVLVAL as height,
C3.heightdate
from CLTCVL as C1
left outer join CLTCVL as C2 on ( C2.CVLCAS = C1.CVLCAS )
inner join
( 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
Results:
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.02235 seconds
] 
Search
Help
Register
Login
Home
