[MonetDB-users] MonetDB LIMIT usage
Hi all, I am trying to use the LIMIT clause in my query, and while this should be fairly straight froward, I am having trouble learning the differences between Monet's LIMIT and other T-sql LIMITS. I have the simple query: SELECT * FROM signatures ORDER BY scale ASC LIMIT 1; which works great and as expected. However, I really want the second record, so in other databases I can use: SELECT * FROM signatures ORDER BY scale ASC LIMIT 1, 1; I have also tried: SELECT * FROM signatures ORDER BY scale ASC LIMIT 1 1; However, these both fail in MonetDB. According to: http://monetdb.cwi.nl/SQL/Documentation/Queries.html#Queries it looks like it should be supported. Does anyone have any insight as to how to grab the second record in MonetDB (I.e., limiting the record results to one with an offset of one)??? I am at a loss here. Cheers, Brett
On 05-01-2011 09:09:58 -0500, Brett Spurrier wrote:
Hi all,
I am trying to use the LIMIT clause in my query, and while this should be fairly straight froward, I am having trouble learning the differences between Monet's LIMIT and other T-sql LIMITS.
I have the simple query: SELECT * FROM signatures ORDER BY scale ASC LIMIT 1;
which works great and as expected. However, I really want the second record, so in other databases I can use: SELECT * FROM signatures ORDER BY scale ASC LIMIT 1, 1;
I have also tried: SELECT * FROM signatures ORDER BY scale ASC LIMIT 1 1;
try this: SELECT * FROM signatures ORDER BY scale ASC OFFSET 1 LIMIT 1;
Thanks Fabian! I'll give that a try. I knew it had to be something simple I
was doing wrong.
Many thanks!
Brett
On Wed, Jan 5, 2011 at 6:21 AM, Fabian Groffen
On 05-01-2011 09:09:58 -0500, Brett Spurrier wrote:
Hi all,
I am trying to use the LIMIT clause in my query, and while this should be fairly straight froward, I am having trouble learning the differences between Monet's LIMIT and other T-sql LIMITS.
I have the simple query: SELECT * FROM signatures ORDER BY scale ASC LIMIT 1;
which works great and as expected. However, I really want the second record, so in other databases I can use: SELECT * FROM signatures ORDER BY scale ASC LIMIT 1, 1;
I have also tried: SELECT * FROM signatures ORDER BY scale ASC LIMIT 1 1;
try this:
SELECT * FROM signatures ORDER BY scale ASC OFFSET 1 LIMIT 1;
------------------------------------------------------------------------------ Learn how Oracle Real Application Clusters (RAC) One Node allows customers to consolidate database storage, standardize their database environment, and, should the need arise, upgrade to a full multi-node Oracle RAC database without downtime or disruption http://p.sf.net/sfu/oracle-sfdevnl _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On 05-01-2011 08:01:00 -0800, Brett Spurrier wrote:
Thanks Fabian! I'll give that a try. I knew it had to be something simple I was doing wrong.
If it doesn't work, try it the other way around (LIMIT 1 OFFSET 1), I always forget which is the right way, and/or if I back then implemented both orders.
On Wed, Jan 05, 2011 at 08:16:26PM +0100, Fabian Groffen wrote:
On 05-01-2011 08:01:00 -0800, Brett Spurrier wrote:
Thanks Fabian! I'll give that a try. I knew it had to be something simple I was doing wrong.
If it doesn't work, try it the other way around (LIMIT 1 OFFSET 1), I always forget which is the right way, and/or if I back then implemented both orders.
According to our SQL syntax description, it is indeed "LIMIT 1 OFFSET 1"; cf., http://monetdb.cwi.nl/SQL/Documentation/Queries.html Stefan
------------------------------------------------------------------------------ Learn how Oracle Real Application Clusters (RAC) One Node allows customers to consolidate database storage, standardize their database environment, and, should the need arise, upgrade to a full multi-node Oracle RAC database without downtime or disruption http://p.sf.net/sfu/oracle-sfdevnl _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | | http://CWI.nl/~manegold/ | Science Park 123 (L321) | | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
participants (3)
-
Brett Spurrier -
Fabian Groffen -
Stefan Manegold