Hey Igor,
Thanks for your replay. I tried not to use user defined function to do it.
This code is working on SqlServer but on SQLite I get misuse of aggregate:
(SUM(SALES)).
SELECT CUSTOMER,
PRODUCT,
[RANK]
FROM (SELECT CUSTOMER,
PRODUCT,
[SUM_SALES],
(SELECT COUNT(T2.SUM_SALES)
FROM (SELECT CUSTOMER,
PRODUCT,
(SUM(SALES)) AS [SUM_SALES]
FROM Test1MX1000Multi AS T1
GROUP BY CUSTOMER,
PRODUCT) AS [T2]
WHERE T1.CUSTOMER = T2.CUSTOMER
AND T2.SUM_SALES > T1.SUM_SALES) AS [RANK]
FROM (SELECT CUSTOMER,
PRODUCT,
(SUM(SALES)) AS [SUM_SALES]
FROM Test1MX1000Multi AS T1
GROUP BY CUSTOMER,
PRODUCT) AS [T1]) AS [TEMP]
WHERE RANK <= 4
ORDER BY CUSTOMER,
RANK
On 10/8/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
>
> 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]
>
> -----------------------------------------------------------------------------
>
>
