I want to explore (=looking at the first 100 rows) databases using the Informix SQL dialect.

  • In Oracle SQL I would use SELCT* FROM table_name WHERE ROWNUM < 100
  • In Postgress SQL I would use SELCT* FROM table_name limit 100
  • I also tried SELCT* FROM table_name first 100

None of these methods works for Informix.

What I found:

One side note: the programm I use will do the SQL call on multiple databases and combine the resulting tables in one table.


2 Answers 2


You can find information about the LIMIT keyword for Informix 12.10 on the IBM site. There are analogous links for the 14.10 documentation. The limit can appear in either (but not both) of two different places within an Informix SELECT statement.

  • SELECT statement
  • LIMIT clause
    • This goes after the ORDER BY clause and before the INTO TEMP clause (if those are present). Otherwise, it goes at the end of the SELECT statement.
  • Projection clause
    • You can use SKIP n and/or either of FIRST n or LIMIT n at the start of the 'select list' after the keywords SELECT.

Note that you should be planning to move from Informix 12.10 to 14.10. It is still supported, but it won't be supported forever.


As ypercubeᵀᴹ mentioned:

Online docs for 14.10 state that LIMIT is available and also for 12.10: https://www.ibm.com/docs/en/informix-servers/12.10?topic=statement-limit-clause

SELECT * FROM table_name LIMIT 100 works.

Why it didn't work at first place, was due to internal peculiarities.

  • 1
    Could you please expand your answer explaining, exactly, what these "internal peculiarities" were?
    – Vérace
    Oct 6, 2022 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.