Bugs item #2895791, was opened at 2009-11-11 08:55
Message generated for change (Tracker Item Submitted) made by alexbod
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2895791&group_id=56967
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: SQL/Core
Group: MonetDB5 "stable"
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Alex Bo (alexbod)
Assigned to: Niels Nes (nielsnes)
Summary: ORDER BY with Row_number() return wrong results
Initial Comment:
(reproduced on Aug-SP1)
Create this table:
create table "table1" ("customer" varchar(40), "product" varchar(40), "price"
double)
insert into "table1" values ('cust1', 'p1', 100)
insert into "table1" values ('cust1', 'p2', 200)
insert into "table1" values ('cust1', 'p3', 150)
insert into "table1" values ('cust2', 'p1', 300)
insert into "table1" values ('cust2', 'p3', 200)
Run this query:
SELECT "customer",
"product",
"sumprice",
(Row_number() OVER(PARTITION BY "customer" ORDER BY "sumprice")) as
"rank"
FROM ( SELECT "customer",
"product",
(Sum("price")) AS "sumprice"
FROM "table1"
GROUP BY "customer",
"product") AS "temp"
It returns:
Returns:
Customer product sumprice rank
Cust1 p1 100 1
Cust1 p2 200 2
Cust1 p3 150 3
Cust2 p1 300 1
Cust2 p3 200 2
But it should return:
Customer product sumprice rank
Cust1 p1 100 1
Cust1 p3 150 2
Cust1 p2 200 3
Cust2 p3 200 1
Cust2 p1 200 2
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2895791&group_id=56967
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
