Get MAX from column
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Get MAX from column

Hybrid View

  1. #1
    Join Date
    May 2009
    Posts
    7

    Get MAX from column

    I'm a novice when it comes to databasing, I'm sure this issue will be easy for you guys, but I wasn't really sure what terms to use for Google search.

    Here's what I have:

    I have 3 tables that I want to work with.. and the relationship between the columns I am concerned with is as follows:

    WTDocument.ida2a2==ObjectHistory.ida3a5
    ObjectHistory.ida3b5==LifeCycleHistory.ida2a2

    I want to find a way to display a column from LifeCycleHistory, that matches the the column WT.Document.ida2a2. The catch here is... ObjectHistory.ida3a5 has a many to 1 relationship with WTDocument.ida2a2.

    So, when I try to do joins, LifeCycleHistory.ida2a2 comes up with several values, depending on how many matches WTDocument.ida2a2==ObjectHistory.ida3a5 results.What I want to do, is obtain one-to-one relationship between WTDocument.ida2a2 and LifeCycleHistory.ida2a2.

    What I was thinking was to obtain the maximum value of ObjectHistory.ida3b5 for each ObjectHistory.ida3a5. Then matching that value with LifeCycleHistory.ida2a2. But, I'm not really sure how...

    ObjectHistory looks something like this:
    ida3a5 ida3b5
    1524511 00615
    1524511 00715
    1524511 00716
    1524511 00816
    1524511 00819
    1524511 00860

    Any help would be GREATLY appreciated!

    Thanks
    Noel

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Not clear to me what are the business specs.

    Does business wants to see the whole history or just a specific event? -in the second case what's the criteria to select the row they want to see?
    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. #3
    Join Date
    May 2009
    Posts
    7

    Business Spec

    The business wants to know the most recent "release date" of a document in the database.

    So, there are three tables that is being used...
    LifeCycleHistory (which has the date at which documents are created),
    WTDocument (which has the name and location of documents),
    ObjectHistory (which is used as a lookup table to match documents to their respective release dates).

    This is how the application was set-up and there are alot more to it, but all I am concerned with is the doc name and its release date.

    I want to create a view that lists the name of the document along with its release date. The problem is... ObjectHistory has a one to many relationship to WTDocument and a one to one relationship to LifeCycleHistory.

    So, when I do a select:

    select LifeCycleHistory LCH,
    WTDocument A4,
    ObjectHistory OBH
    where A4.ida2a2=OBH.ida3a5 and OBH.ida3b5=LCH.ida2a2

    I get more than one return of the document, if it has several release dates in its history. But, all I want is the MOST recent.

    I hope that helped clarify alittle?

    Thanks again,
    Noel
    Last edited by ltsuek; 05-21-2009 at 04:31 PM. Reason: edit select statement syntax

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Take a look at the max() function

    max() function alongside an inline view would do the trick.
    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.

  5. #5
    Join Date
    May 2009
    Posts
    7

    Tried MAX function

    I've reviewed over the mac function and looking to try to use this in combination with an inline view.

    Basically, I want to be able to pull just a single date for each unique WT.ida2a2. There are several tables that I need to combine and apply filters for. I want to show the document title, name, and latest release date. Each time a document is released, an entry is created in the LIFECYCLEHISTORY table, only way of know what the release date of the document is, is to match the LIFECYCLEHISTORY.ida2a2 with OBJECTHISTORY.ida3b5.. then match the OBJECTHISTORY.ida3a5 with the WT.ida2a2.

    Here is what I have, I know there is something wrong... just can't figure it out:

    select distinct
    A4.TITLE "DOC_TITLE" ,
    DM.wtdocumentnumber "DOC_NUMBER" ,
    LCH.UPDATESTAMPA2 "RELEASE_DATE"

    from
    wc.WT A4 ,
    wc.wtdocumentMaster DM ,
    (select max(wc.LIFECYCLEHISTORY.UPDATESTAMPA2) from wc.WT
    join wc.OBJECTHISTORY on wc.WT.IDA2A2 = wc.OBJECTHISTORY.IDA3A5
    join wc.LIFECYCLEHISTORY on wc.OBJECTHISTORY.IDA3B5 = wc.LIFECYCLEHISTORY.IDA2A2
    group by wc.LIFECYCLEHISTORY.UPDATESTAMPA2)

    where

    A4.STATESTATE IN ('RELEASED'))
    A4.LATESTITERATIONINFO = 1 and

    order by
    1,2,;

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by ltsuek View Post
    I know there is something wrong...
    You are correct, something is wrong but you have worked on it -we can appreciate that.

    Here is how you solve the problem combining max() and inline view...

    Code:
    select  m.column-a,
            m.column-b,
            v.column-date
    from    master m,
            variable v
    where   m.column-a = v.column-a
    and     v.column_date = (
                            select  max(x.column-date)
                            from    variable x
                            where   x.column-a = m.column-a
                            )
    ;
    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.

  7. #7
    Join Date
    May 2009
    Posts
    7

    Still getting multiple results

    I added the line of code reccomended:

    select m.column-a,
    m.column-b,
    v.column-date
    from master m,
    variable v
    where m.column-a = v.column-a
    and v.column_date = (
    select max(x.column-date)
    from variable x
    where x.column-a = m.column-a
    )
    ;

    I am still pulling multiple results for a single document title. I think there should be a "group by" somewhere in the inner select statement. I've tried "group by x.column-a, but results were the same, still multiple documents with multiple release dates.

    Thanks.

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Please post -formatted in between code tags...

    1- desc of affected tables, please include any PK or unique index
    2- cardinality for each table
    3- offending query
    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.

  9. #9
    Join Date
    May 2009
    Posts
    7

    More Info

    Here is a segment of the code in question:

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

    OBJECTHISTORY table: IDA2A2 column is Primary Key
    UPDATESTAMPDATEA2 is a Date data type all others are numbers.
    This is the "reference/look-up" table to match lifecycles with their respective document titles
    IDA3A5 = WT.IDA2A2
    IDA3B5 = LIFECYCLEHISTORY.IDA2A2

    LIFECYCLEHISTORY table: IDA2A2 column is primary key
    UPDATESTAMPA2 is a Date data type all others are numbers, this column lists the date that we want.

    WT table IDA2A2 column is primary key
    TITLE column is what we want as the result adjacent to LIFECYCLEHISTORY.UPDATESTAMPA2

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    WHERE clause lacks how to join tables wc.WT and wc.OBJECTHISTORY -check again template sent before.

    No need for GROUP BY clause on inline view.

    No need for DISTINCT clause on main query.
    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.

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