Re: how to get specific values from multi-table function

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sun, 13 Jan 2008 17:12:31 +0100
Message-ID: <478A386F.5000301@gmail.com>

Charles Hooper schrieb:
> On Jan 12, 7:45 pm, Totti <saliba.toufic.geo..._at_gmail.com> wrote:

>> hi all,
>> i am posting a sample of a result i am getting from the following
>> formula
>> ---------------------------------------------------------------------------­---------------------------
>> select (to_char(salinv_1.inv_date, 'MM'))as "MM",
>> (to_char(salinv_1.inv_date, 'YY')) as "YY",
>> (sales_1.prod_code) as "Code",
>> sum(salpmt_1.amount) as"Total Sales"
>> from sales_1,salinv_1, salpmt_1
>> where salinv_1.code = sales_1.inv_code and
>> to_char(salpmt_1.pmt_date,'MM-YY') = to_char(salinv_1.inv_date, 'MM-
>> YY')
>> group by (sales_1.prod_code),
>> (to_char(salinv_1.inv_date, 'MM')),
>> (to_char(salinv_1.inv_date, 'YY'))
>> order by (to_char(salinv_1.inv_date,'YY'))
>> ---------------------------------------------------------------------------­---------------------------
>> -- Results
>> ---------------------------------------------------------------------------­---------------------------
>> 12 05 SP-20.6              453772
>> 12 05 SP-20.7              288764
>> 01 06 SP-20.1              258731
>> 01 06 SP-20.2              282252
>> 01 06 SP-20.6              188168
>> 02 06 SP-20.4               55868
>> 02 06 SP-20.5               97769
>> 03 06 SP-20.1              178960
>> 03 06 SP-20.2              178960
>> ---------------------------------------------------------------------------­---------------------------
>> the results go so for every product monthly till the end;
>> i need the best product monthly, so this would be the max of sales for
>> each month;
>> meaning 1 prod/month with max sales;
>> can you please tell me how would this be possible?
>>
>> thanks

>
> The following solution/approach will require analytical functions.
> First, I will set up a simple table that contains the results of your
> current SQL statement, but I will leave INV_DATE as a single column
> rather than as two columns:
> CREATE TABLE T1(
> INV_DATE DATE,
> PROD_CODE VARCHAR2(12),
> TOTAL_SALES NUMBER(22,2));
>
> INSERT INTO
> T1
> VALUES(
> TO_DATE('12 05','MM-YY'),
> 'SP-20.6',
> 453772);
>
> INSERT INTO
> T1
> VALUES(
> TO_DATE('12 05','MM-YY'),
> 'SP-20.7',
> 288764);
>
> INSERT INTO
> T1
> VALUES(
> TO_DATE('01 06','MM-YY'),
> 'SP-20.1',
> 258731);
>
> INSERT INTO
> T1
> VALUES(
> TO_DATE('01 06','MM-YY'),
> 'SP-20.2',
> 282252);
>
> INSERT INTO
> T1
> VALUES(
> TO_DATE('01 06','MM-YY'),
> 'SP-20.6',
> 188168);
>
> INSERT INTO
> T1
> VALUES(
> TO_DATE('02 06','MM-YY'),
> 'SP-20.4',
> 55868);
>
> INSERT INTO
> T1
> VALUES(
> TO_DATE('02 06','MM-YY'),
> 'SP-20.5',
> 97769);
>
> INSERT INTO
> T1
> VALUES(
> TO_DATE('03 06','MM-YY'),
> 'SP-20.1',
> 178960);
>
> INSERT INTO
> T1
> VALUES(
> TO_DATE('03 06','MM-YY'),
> 'SP-20.2',
> 178960);
>
> Now that I have a simple table to use for experimentation, I can try a
> quick experiment to see if I can find a solution:
> SELECT
> INV_DATE,
> PROD_CODE,
> TOTAL_SALES,
> ROW_NUMBER() OVER (PARTITION BY INV_DATE ORDER BY TOTAL_SALES DESC)
> RN
> FROM
> T1;
>
> INV_DATE PROD_CODE TOTAL_SALES RN
> --------- ------------ ----------- ----
> 01-DEC-05 SP-20.6 453772 1
> 01-DEC-05 SP-20.7 288764 2
> 01-JAN-06 SP-20.2 282252 1
> 01-JAN-06 SP-20.1 258731 2
> 01-JAN-06 SP-20.6 188168 3
> 01-FEB-06 SP-20.5 97769 1
> 01-FEB-06 SP-20.4 55868 2
> 01-MAR-06 SP-20.1 178960 1
> 01-MAR-06 SP-20.2 178960 2
>
> The above simply lists the data in the T1 table, and adds a counter
> for each INV_DATE (specified by the PARTITION BY) that starts at 1 and
> counts upward based on the decreasing TOTAL_SALES amount (specified by
> the ORDER BY TOTAL_SALES DESC). The ROW_NUMBER() analytical function
> makes this easy.
>
> The month of March 2006 has the same TOTAL_SALES for two PROD_CODEs,
> and the value of RN could pick either of those PROD_CODEs as 1, and
> the other as 2. After the "ORDER BY TOTAL_SALES DESC" in the SQL
> statement, it might be a good idea to also order by the PROD_CODE
> column by adding ",PROD_CODE" to the ORDER BY clause so that it is
> easy to predict the PROD_CODE that will be displayed.
>
> Now, we can slide the above SQL statement into an inline view to find
> only those rows where RN (the result of the ROW_NUMBER() analytical
> function) is equal to 1:
> SELECT
> INV_DATE,
> PROD_CODE,
> TOTAL_SALES
> FROM
> (SELECT
> INV_DATE,
> PROD_CODE,
> TOTAL_SALES,
> ROW_NUMBER() OVER (PARTITION BY INV_DATE ORDER BY TOTAL_SALES
> DESC) RN
> FROM
> T1)
> WHERE
> RN=1;
>
> INV_DATE PROD_CODE TOTAL_SALES
> --------- ------------ -----------
> 01-DEC-05 SP-20.6 453772
> 01-JAN-06 SP-20.2 282252
> 01-FEB-06 SP-20.5 97769
> 01-MAR-06 SP-20.1 178960
>
> Now, how do you make the above work with your SQL statement? First,
> change the column aliases so that they do not contain spaces (change
> "Total Sales" to TOTAL_SALES). Then, slide your current SQL statement
> into an inline view (like I did in the last example). You can then
> start experimenting with the ROW_NUMBER analytical function.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Nice example (as usual).
Here is another one, based on aggregate functions.

If the only required information would be to find top sales monthly, then it is relatively easy (using Charles' test data):

select inv_date,max(total_sales)
  from t1 group by inv_date

However, because another attribute (prod_code) is of interest, it is not enough. The standard approach to solve such task :

select inv_date,prod_code,total_sales
from t1
where (inv_date,total_sales) in
(select inv_date,max(total_sales)
  from t1 group by inv_date
)

Since Oracle 9i, the need in the subquery is eliminated due to FIRST/LAST functions ( which exists in both - analytical and aggregate - form):

select inv_date,
max(prod_code) keep(dense_rank last order by total_sales), max(total_sales)
from t1
group by inv_date

In case of ambiguos ordering criteria ( that is - if more than one different products are topsellers, i.e. have exactly the same total_sales) the result may be not as expected (it is valid for all approaches - the traditional subquery, first/last or analytical approach with numbering of rows by means of row_number(), rank() or dense_rank() , in this case the query should be adjusted according to business requirements - what should be returned as top seller, both products, any one of them, none...)

Best regards

Maxim Received on Sun Jan 13 2008 - 10:12:31 CST