Aviad Harell <[EMAIL PROTECTED]>
wrote:
I tried to create user defined function called Rank. which gets
partition columns and order by column. this function should create
rank according to its parameters. i.e.:
select customer, product, sales, Rank(customer, sales)
from table
should return:
customerA productA 100 0
customerA productB 300 2
customerA productC 200 1
customerA productD 400 3
customerB productB 300 1
customerB productA 400 2
customerB productC 100 0
how can i do it? should i use FunctionType scalar or aggregate?
Scalar. Aggregate function looks at multiple rows and produces a single
value (see SUM, MAX). A scalar function produces one value per row,
which is what you want here.
Note also that you don't really need a custom function here. This query
should produce the same result:
select customer, product, sales,
(select count(*) from tableName t2
where t2.customer = t1.customer and t2.sales < t1.sales)
from tableName t1;
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
