Re: Another Messed Up Query
From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 9 Jan 2009 13:34:41 -0800 (PST)
Message-ID: <653378ad-a837-4499-bc63-0c815dd2edd8_at_i18g2000prf.googlegroups.com>
On Jan 9, 3:21 pm, mrdjmag..._at_aol.com wrote:
> This must be a bad day for me. Here are the 2 tables:
>
> TABLE A
> --------------
> ID MONTH YEAR TOTAL_RETURN
> -------------------------------------------------------------
> A 1 1997 56
> A 2 1997 88
> A 3 1997 98
> A 1 1998 34
> A 2 1998 11
> A 3 1998 97
>
> TABLE B
> --------------
> ID PRICE DATE_ENTERED
> --------------------------------------------
> A .56 01/14/1997
> A .98 01/18/1997
> A .30 02/23/1997
> A .52 01/14/1998
> A .11 02/03/1998
> A .34 02/09/1998
>
> Ok, so you see the pattern which occurs for thousands of records.
> TABLE A has data by Month & Year.
> TABLE B has multiple records per month.
> Tables are joined by ID.
>
> I need to get the ID & Total Return from TABLE A and Price & MAX
> (LAST_DAY(date_entered)) from TABLE B FOR EACH MONTH/YEAR in TABLE A.
>
> So, the result should be:
> A .98 56 01/31/1997
> A .30 88 02/28/1997
> .
> .
> .
> I need the MAX date in TABLE B for each MONTH/YEAR combination in
> TABLE A.....
>
> Possible in 1 query??? Tried analytical functions with no luck.
Date: Fri, 9 Jan 2009 13:34:41 -0800 (PST)
Message-ID: <653378ad-a837-4499-bc63-0c815dd2edd8_at_i18g2000prf.googlegroups.com>
On Jan 9, 3:21 pm, mrdjmag..._at_aol.com wrote:
> This must be a bad day for me. Here are the 2 tables:
>
> TABLE A
> --------------
> ID MONTH YEAR TOTAL_RETURN
> -------------------------------------------------------------
> A 1 1997 56
> A 2 1997 88
> A 3 1997 98
> A 1 1998 34
> A 2 1998 11
> A 3 1998 97
>
> TABLE B
> --------------
> ID PRICE DATE_ENTERED
> --------------------------------------------
> A .56 01/14/1997
> A .98 01/18/1997
> A .30 02/23/1997
> A .52 01/14/1998
> A .11 02/03/1998
> A .34 02/09/1998
>
> Ok, so you see the pattern which occurs for thousands of records.
> TABLE A has data by Month & Year.
> TABLE B has multiple records per month.
> Tables are joined by ID.
>
> I need to get the ID & Total Return from TABLE A and Price & MAX
> (LAST_DAY(date_entered)) from TABLE B FOR EACH MONTH/YEAR in TABLE A.
>
> So, the result should be:
> A .98 56 01/31/1997
> A .30 88 02/28/1997
> .
> .
> .
> I need the MAX date in TABLE B for each MONTH/YEAR combination in
> TABLE A.....
>
> Possible in 1 query??? Tried analytical functions with no luck.
It would be helpful to:
* Indicate the Oracle version
* List the DDL and DML statements to create the tables with data you
listed
* Show us what you have tried so far
If you select all columns in TABLE B, and also select the following, do you receive useful information on every row where ROW_POSITION=1? ROW_NUMBER() OVER (PARTITION BY ID,TRUNC(DATE_ENTERED,'MM') ORDER DATE_ENTERED DESC) ROW_POSITION If you do, how might you select only those rows from TABLE B where ROW_POSITION=1? (Hint: inline view). You could then join the inline view to TABLE A, assuming that you used the LAST_DAY function inside the inline view.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Fri Jan 09 2009 - 15:34:41 CST