 | |
how to get specific values from multi-table function
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: how to get specific values from multi-table function -
01-13-2008
, 11:24 AM
|
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 |
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.
#12
| |||
| |||
|
|
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 |
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.
#13
| |||
| |||
|
|
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 |
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.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.