Get MAX from column - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Get MAX from column

  1. #11
    Join Date
    May 2009
    Posts
    7
    Ok, after changes were made, I am still getting multiple release dates for 1 document title.

    How does the system know that we want only the max date in LCH for unique ID's only?

    Here's a sample of the tables:

    WT table:

    IDA2A2: TITLE
    5455135 TEST1
    1546413 TEST2
    6754313 TEST3
    3575313 TEST4
    2484613 TEST5
    4681843 TEST6

    OBJECTHISTORY table:

    IDA2A2: IDA3A5: IDA3B5:
    8761649 5455135 6876468
    3516463 1546413 6761393
    3516464 1546413 6761394
    3516465 1546413 6761395
    3516466 1546413 6761396
    4346796 6754313 3213946
    4346797 6754313 3213947
    49/8761 3575313 6484313
    6846946 2484613 1318433
    4646846 4681843 6313873

    LIFECYCLEHISTORY table:

    IDA2A2: UPDATESTAMPA2
    6876468 MAY 8 2009
    6761393 SEP 20 2006
    6761394 JUN 1 2007
    6761395 DEC 2 2005
    6761396 DEC 15 2005
    3213946 DEC 9 2004
    3213947 NOV 2 2003
    6484313 OCT 24 2006
    1318433 JAN 26 2007
    6313873 JAN 17 2005


    Code:
    select 	
    	A4.TITLE 		"DOC_TITLE"		,
    	A4.DESCRIPTION		"DOC_DESC"		,
         	OBH.UPDATESTAMPA2	"RELEASE_DATE"               
    from
    	wc.WT 			A4	,
    	wc.OBJECTHISTORY	OBH     
               
                
    where
    	A4.ida2a2               = OBH.ida3a5                  and
    	OBH.UPDATESTAMPA2	= (
    				select max(LCH.UPDATESTAMPA2) 
    				from wc.LIFECYCLEHISTORY LCH
                                    where LCH.ida2a2 = OBH.ida3b5
                                    )  
    order by
          1,3

  2. #12
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Don't think posted query even parses.

    Column UPDATESTAMPA2 selected from ObjectHistory doesn't even exist in that table -that column belongs to LifeCycleHistory table.

    If query has to return UPDATSTAMPA2 then LifeCycleHistory table has to be part of the main query.

    There is no direct relationship in between WT and LifeCycleHistory tables but over the ObjectHistory bridge then query has to include ObjectHistory table in the main query alongside WT and LifeCycleHistory tables.

    Since ObjectHistory is the table that would give you the last incarnation id of the document e.g. max(IDA3B5) then inline view has to go against ObjectHistory table -inline view would single out a unique row to be returned.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #13
    Join Date
    May 2009
    Posts
    7
    I'm a bit confused on this. How would the inline view look then? Shouldn't the MAX() function be used on the OBJECTHISTORY.ida3a5, since it is the column with multiple values?

    I want to be able to cross reference that column with OBJECTHISTORY.ida3b5, since ida3b5 is the value that matches with LifecycleHistory. I want to just match 1 OBJECTHISTORY.ida3b5 with LifeCycleHistory.ida2a2...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width