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

On Jan 12, 7:45*pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
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.

On Jan 12, 7:45*pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
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.

On Jan 12, 7:45*pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
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.

On Jan 12, 7:45*pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
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.

Charles Hooper schrieb:
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

Charles Hooper schrieb:
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

Charles Hooper schrieb:
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

Charles Hooper schrieb:
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

On Jan 13, 11:12*am, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:
max(prod_code) keep(dense_rank last order by total_sales)...

Thanks for posting the above explanation. I was not aware of the KEEP
syntax, and now I must again read the Oracle SQL Reference
documentation to see what else I missed.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.