[MonetDB-users] Row_Number() over problem

18 Mar 2009

      Hi,
Given the following table:

create table "table1" (col1 varchar(20), col2 int)

insert into "table1" values ('a1', 1)
insert into "table1" values ('a2', 3)
insert into "table1" values ('a3', 5)
insert into "table1" values ('a4', 6)
insert into "table1" values ('a5', 3)
insert into "table1" values ('a6', 7)

The following query:

select col1, "SumCol2", "rank" from (
select col1, "SumCol2", (row_number() over(order by "SumCol2")) as "rank"
from (
select col1, (sum(col2)) as "SumCol2"
from "table1"
group by col1) as "temp1") as "temp2"

Results:
            col1        SumCol2  rank
	a1	1	1
	a2	3	3
	a3	5	4
	a4	6	5
	a5	3	2
	a6	7	6

This is good result set.
But if one adds this query a where clause that involves the rank column,
empty result set is returned. Example:

select col1, "SumCol2", "rank" from (
select col1, "SumCol2", (row_number() over(order by "SumCol2")) as "rank"
from (
select col1, (sum(col2)) as "SumCol2"
from "table1"
group by col1) as "temp1") as "temp2"
where rank = 1

any ideas?

Thanks Alfred

-- 
View this message in context: http://www.nabble.com/Row_Number%28%29-over-problem-tp22577176p22577176.html
Sent from the monetdb-users mailing list archive at Nabble.com.

[MonetDB-users] Row_Number() over problem

Alfred Nordman