Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Getting max date when joining a table to itself
Getting max date when joining a table to itself [message #903377] Thu, 23 August 2012 08:34 Go to next message
Michael Harless is currently offline 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.
Re: Getting max date when joining a table to itself [message #903392 is a reply to message #903377] Thu, 23 August 2012 09:16 Go to previous messageGo to next message
Michael Harless is currently offline Michael Harless
Messages: 14
Registered: January 2012
Junior Member
I should add that the max date on the weight and the height are not the same. They differ by minutes/seconds in their actual values.
Re: Getting max date when joining a table to itself [message #903632 is a reply to message #903377] Fri, 24 August 2012 11:16 Go to previous messageGo to next message
Michael Harless is currently offline Michael Harless
Messages: 14
Registered: January 2012
Junior Member
BUMP... ANYONE?
Re: Getting max date when joining a table to itself [message #904088 is a reply to message #903632] Mon, 27 August 2012 14:23 Go to previous message
Mica J. Block is currently offline Mica J. Block
Messages: 3
Registered: July 2009
Junior Member
Are you looking to get just the first row?

Also, would this query return a single row?

select
C1.CVLVAL as weight,
max (C1.CVLDATE) as entered

from CLTCVL as C1

where C1.CVLTYPE = '1'
and C1.CVLCAS = ?

[Updated on: Mon, 27 August 2012 14:28]

Report message to a moderator

Previous Topic:dependant, nested tables
Next Topic:Problem with scatter charts
Goto Forum:
  


Current Time: Thu Jul 31 23:54:49 EDT 2014

Powered by FUDForum. Page generated in 0.02157 seconds