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.