Hi Alfred, Which version of MonetDB/SQL are you using? What does `mserver5 --version` say? With the latest CVS codebase from the Feb2009 release branch $ mserver5 --version MonetDB server v5.10.1 (64-bit), based on kernel v1.28.1 (64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2009 MonetDB B.V., all rights reserved Visit http://monetdb.cwi.nl/ for further information Configured for prefix: /ufs/manegold/_/scratch0/Monet/Testing/Stable/prefix.--enable-strict_--disable-debug_--enable-optimize_--disable-assert Libraries: libpcre: 7.8 2008-09-05 (compiled with 7.8) openssl: OpenSSL 0.9.8g 19 Oct 2007 (compiled with OpenSSL 0.9.8g 19 Oct 2007) libxml2: 2.7.3 (compiled with 2.7.3) Compiled by: manegold@rig.ins.cwi.nl Compilation: gcc -O2 -Wall -Wextra -std=c99 -O6 -fomit-frame-pointer -finline-functions -falign-loops=4 -falign-jumps=4 -falign-functions=4 -fexpensive-optimizations -funroll-loops -frerun-cse-after-loop -frerun-loop-opt -ftree-vectorize -Werror-implicit-function-declaration -Werror -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wp,-D_FORTIFY_SOURCE=2 Linking : ld -IPA -m elf_x86_64 I get: $ mclient -lsql sql>create table "table1" (col1 varchar(20), col2 int); 0 tuples sql>insert into "table1" values ('a1', 1); Rows affected 1 sql>insert into "table1" values ('a2', 3); Rows affected 1 sql>insert into "table1" values ('a3', 5); Rows affected 1 sql>insert into "table1" values ('a4', 6); Rows affected 1 sql>insert into "table1" values ('a5', 3); Rows affected 1 sql>insert into "table1" values ('a6', 7); Rows affected 1 sql>select col1, "SumCol2", "rank" from ( more>select col1, "SumCol2", (row_number() over(order by "SumCol2")) as "rank" more>from ( more>select col1, (sum(col2)) as "SumCol2" more>from "table1" more>group by col1) as "temp1") as "temp2"; +------+---------+------+ | col1 | SumCol2 | rank | +======+=========+======+ | a1 | 1 | 1 | | a2 | 3 | 3 | | a3 | 5 | 4 | | a4 | 6 | 5 | | a5 | 3 | 2 | | a6 | 7 | 6 | +------+---------+------+ 6 tuples sql>select col1, "SumCol2", "rank" from ( more>select col1, "SumCol2", (row_number() over(order by "SumCol2")) as "rank" more>from ( more>select col1, (sum(col2)) as "SumCol2" more>from "table1" more>group by col1) as "temp1") as "temp2" more>where rank = 1; +------+---------+------+ | col1 | SumCol2 | rank | +======+=========+======+ | a1 | 1 | 1 | +------+---------+------+ 1 tuple looks fine to me ... Stefan On Wed, Mar 18, 2009 at 04:31:35AM -0700, Alfred Nordman wrote:
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.
------------------------------------------------------------------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |