Bugs item #2895791, was opened at 2009-11-11 08:55
Message generated for change (Comment added) made by nielsnes
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: Closed
>Resolution: Fixed
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
----------------------------------------------------------------------
>Comment By: Niels Nes (nielsnes)
Date: 2009-11-15 21:40
Message:
fixed by correctly ordering (and projecting back) the to be ranked (and
ranked) column
----------------------------------------------------------------------
Comment By: Niels Nes (nielsnes)
Date: 2009-11-12 07:42
Message:
added test to orderby_with_row_number.SF-2895791.sql
----------------------------------------------------------------------
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
