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

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. Received on Sun Jan 13 2008 - 08:28:58 CST