[MonetDB-users] Row_Number() over problem
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.
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 |
Hi Stefan and thank you for the quick response. I’m using: server v5.9.0, based on kernel v1.27.0 I see that yours is newer. I’ll download the newer version and check it there. Thanks, Alfred. Stefan Manegold wrote:
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 |
------------------------------------------------------------------------------ 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
-- View this message in context: http://www.nabble.com/Row_Number%28%29-over-problem-tp22577176p22577517.html Sent from the monetdb-users mailing list archive at Nabble.com.
Hi Stefan and thank you for the quick response. I’m using: server v5.9.0, based on kernel v1.27.0 I see that yours is newer. I’ll download the newer version and check it there. Thanks, Alfred. Stefan Manegold wrote:
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 |
------------------------------------------------------------------------------ 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
-- View this message in context: http://www.nabble.com/Row_Number%28%29-over-problem-tp22577176p22578536.html Sent from the monetdb-users mailing list archive at Nabble.com.
i've downloaded the latest version and its working!!!! thanks Alfred. Stefan Manegold wrote:
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 |
------------------------------------------------------------------------------ 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
-- View this message in context: http://www.nabble.com/Row_Number%28%29-over-problem-tp22577176p22582316.html Sent from the monetdb-users mailing list archive at Nabble.com.
Now there's something else, with the updated version: In the query I wrote before, If I add a cast to row_number() as DOUBLE, then the same query as before doesn't return any results. If I cast it as INT, I do get results. Why is that? Thanks Alfred. -- View this message in context: http://www.nabble.com/Row_Number%28%29-over-problem-tp22577176p22601449.html Sent from the monetdb-users mailing list archive at Nabble.com.
Alfred, Would you mind sharing your original query as well as the modified one (again) with us? --- That's much simpler (for us) than searching backwards through our email archives ... ... also your create table statement(s) might be helpful ... Thanks! Stefan On Thu, Mar 19, 2009 at 08:22:54AM -0700, Alfred Nordman wrote:
Now there's something else, with the updated version:
In the query I wrote before, If I add a cast to row_number() as DOUBLE, then the same query as before doesn't return any results.
If I cast it as INT, I do get results.
Why is that?
Thanks Alfred. -- View this message in context: http://www.nabble.com/Row_Number%28%29-over-problem-tp22577176p22601449.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 |
Stefan, Sure I can. For the given 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" where rank = 1 Works fine. But if I modify this query so the row_number() over column is casted to double, the results empty result set: select col1, "SumCol2", "rank" from ( select col1, "SumCol2", (cast(row_number() over(order by "SumCol2") as double)) as "rank" from ( select col1, (sum(col2)) as "SumCol2" from "table1" group by col1) as "temp1") as "temp2" where rank = 1 Thanks, Alfred. Stefan Manegold wrote:
Alfred,
Would you mind sharing your original query as well as the modified one (again) with us? --- That's much simpler (for us) than searching backwards through our email archives ...
... also your create table statement(s) might be helpful ...
Thanks!
Stefan
On Thu, Mar 19, 2009 at 08:22:54AM -0700, Alfred Nordman wrote:
Now there's something else, with the updated version:
In the query I wrote before, If I add a cast to row_number() as DOUBLE, then the same query as before doesn't return any results.
If I cast it as INT, I do get results.
Why is that?
Thanks Alfred. -- View this message in context: http://www.nabble.com/Row_Number%28%29-over-problem-tp22577176p22601449.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 |
------------------------------------------------------------------------------ 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
-- View this message in context: http://www.nabble.com/Row_Number%28%29-over-problem-tp22577176p22644634.html Sent from the monetdb-users mailing list archive at Nabble.com.
participants (2)
-
Alfred Nordman -
Stefan Manegold