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

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?

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

Take a look at the max() function

max() function alongside an inline view would do the trick.

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. :confused:

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,;

Quote:

---

Originally Posted by ltsuek

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
                        )
;

---

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.

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

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

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.

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

---

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.

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...