-
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?
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.
-
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
-
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.
-
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,;
-
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
)
;
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.
-
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.
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|