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

On Jan 13, 11:12 am, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> 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

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. Received on Sun Jan 13 2008 - 11:24:17 CST