Aviad Harell <[EMAIL PROTECTED]>
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]

Reply via email to