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] > > ----------------------------------------------------------------------------- > >