Login Register Actian.com  

Actian Community Forum



Reply
 
LinkBack Thread Tools Search this Thread Display Modes
Old 2008-07-17   #1 (permalink)
Junior Member
 
Join Date: Jul 2008
Posts: 11
Default limit function

Dear All!

Is there a simular function to ingres like "limit" in mysql???

Thank you in advance!

maddilli16
maddilli16 is offline   Reply With Quote
Old 2008-07-17   #2 (permalink)
Actian Corp
 
Join Date: Mar 2007
Location: Australia
Posts: 345
Blog Entries: 1
Default

You need:

FETCH FIRST n OFFSET m

which is equivalent to limit n,m. This functionality is available only in the latest release currently in Beta, you can download the Beta release of Ingres 2006 Release 3 (9.2) from here:

http://www.ingres.com/downloads/community-editions.php

I have included the instructions on using this feature from our current draft of the Release Guide

---------------------------------------------------------------------------------------
Fetch First n and Offset n

The OFFSET clause and FETCH FIRST clause on the SELECT statement can be used to return a subset of rows from a result set.

Users can code the ??n? value of a FETCH FIRST n or OFFSET n specification in a SELECT statement as a host language variable in embedded SQL applications, or as a parameter or local variable in a database procedure.
This feature is useful in Web-style applications that page results back to the user, as in the results from using a web search engine.

For example, the following query returns rows starting from the 25th row of the result set:

SELECT * FROM MYTABLE ORDER BY COL1 OFFSET 25

For example, the following query fetches only the first 10 rows of the result set:

SELECT * FROM MYTABLE ORDER BY COL1 FETCH FIRST 10 ROWS ONLY

A query can use any combination of the ORDER BY, OFFSET, and FETCH FIRST clauses, but in that order only.

The OFFSET and FETCH FIRST clauses can be used only once per query, and cannot be used in unions or view definitions. They cannot be used in subselects, except a subselect in a CREATE TABLE statement or an INSERT statement.

The FETCH FIRST clause cannot be used in the same SELECT statement as SELECT FIRST rowcount.

The syntax for each clause is OFFSET n or FETCH FIRST n, where n is a positive integer, a host variable, or a procedure parameter or local variable.

In the FETCH FIRST clause, the keywords FIRST and NEXT, and the keywords ROWS and ROW are interchangeable. Because you can offset and fetch first in the same query, NEXT is an alternative for readability. For example:

OFFSET 10 FETCH NEXT 25 ROWS ONLY
stephenb is offline   Reply With Quote
Old 2008-07-18   #3 (permalink)
Junior Member
 
Join Date: Jul 2008
Posts: 11
Default

Thank you for your fast answer!
i installed the Ingres 2006 Release 3 (9.2) last week and i fired following command on the visual sql:

select prim1, prim2 from test group by prim1, prim2 fetch first 2 rows only

i get the following error:

E_US0A10 line 1, Syntax error on 'fetch'. The correect syntax is:
PREPARE stmnt_name [INTO name] FROM statement_text

My table test --> prim1 prim2 field1 field2
maddilli16 is offline   Reply With Quote
Old 2008-07-18   #4 (permalink)
Junior Member
 
Join Date: Apr 2008
Posts: 7
Default

Example of the usage with ingres;

Quote:
* select first 3 table_name,table_owner from iitables\g
Executing . . .


????????????????????????????????? ????????????????????????????????? ????????????????????????????????? ?????????????????????????????????
??table_name ??table_owner ??
????????????????????????????????? ????????????????????????????????? ????????????????????????????????? ?????????????????????????????????
??iiprotect ??$ingres ??
??iiddb_netcost ??$ingres ??
??iirole ??$ingres ??
????????????????????????????????? ????????????????????????????????? ????????????????????????????????? ????????????????????????????????? ?
(3 rows)
boija02 is offline   Reply With Quote
Old 2008-07-18   #5 (permalink)
Junior Member
 
Join Date: Apr 2008
Posts: 7
Default

With ingres tm/visual sql, that is.
boija02 is offline   Reply With Quote
Old 2008-07-18   #6 (permalink)
Junior Member
 
Join Date: Apr 2008
Posts: 7
Default

Looking at the sql reference guide, the "fetch first n rows only" version is equally valid;

Quote:
* select table_name from iitables fetch first 2 rows only\g
Executing . . .


????????????????????????????????? ????????????????????????????????? ?
??table_name ??
????????????????????????????????? ????????????????????????????????? ?
??iiprotect ??
??iiddb_netcost ??
????????????????????????????????? ????????????????????????????????? ??
(2 rows)

* select table_name,table_owner from iitables group by table_name, table_owner fetch first 2 rows only\g
Executing . . .


????????????????????????????????? ????????????????????????????????? ????????????????????????????????? ?????????????????????????????????
??table_name ??table_owner ??
????????????????????????????????? ????????????????????????????????? ????????????????????????????????? ?????????????????????????????????
??ii_abfclasses ??$ingres ??
??ii_abfdependencies ??$ingres ??
????????????????????????????????? ????????????????????????????????? ????????????????????????????????? ????????????????????????????????? ?
(2 rows)
That's with 9.2.0 build127, which isn't posted on the community site yet. It's possible there were fixes in this area between 118 and 127, so you may need to wait for a refresh. Does "select first n..." work?
boija02 is offline   Reply With Quote
Old 2008-07-19   #7 (permalink)
Junior Member
 
Join Date: Jul 2008
Posts: 11
Default

Yes, i have the build 118. So i have to wait for a refresh.

The second oportunity works. --> SELECT FIRST 3 * FROM table.
But, how can i define an offset. For example i want to choose the 7th to 10th line of the resultset. Is there any oportunity to do this?
maddilli16 is offline   Reply With Quote
Old 2008-07-22   #8 (permalink)
Actian Corp
 
Join Date: Mar 2007
Location: Australia
Posts: 345
Blog Entries: 1
Default

I haven't tested it, but you should be able to do that; an example would be:

SELECT * FROM table OFFSET 7 FETCH NEXT 3 ROWS ONLY

Maybe the GROUP BY syntax is complicating your example, try putting it at the end.
stephenb is offline   Reply With Quote

Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


© 2011 Actian Corporation. All Rights Reserved