2

Does informix has a function similar to the SQLServer and Oracle's row_number()? I have to make a query using row_number() between two values, but I don't know how.

This is my query in SQLServer:

SELECT col1, col2 
FROM (SELECT col1, col2, ROW_NUMBER() 
OVER (ORDER BY col1) AS ROWNUM FROM table) AS TB 
WHERE TB.ROWNUM BETWEEN value1 AND value2

Some help?

Guilherme Oliveira
  • 2,008
  • 3
  • 27
  • 44
  • possible duplicate of [Row numbers for a query in informix](http://stackoverflow.com/questions/119278/row-numbers-for-a-query-in-informix) – Adriano Carneiro Sep 14 '11 at 17:38
  • Although the possible duplicate question does mention ROW_NUMBER(), one aspect of the problem here is the OVER clause, which is certainly not mentioned in the other question. – Jonathan Leffler Sep 14 '11 at 18:08
  • Is this query seeking to get row numbers 10-19, then later 20-29, then ...? If so, then you should be looking at SKIP and FIRST/LIMIT. – Jonathan Leffler Sep 14 '11 at 18:10
  • Yes, at first moment get the 100 first numbers, after get 101-200...Thanks for helping me, I'll try to use Skip and First/Limit – Guilherme Oliveira Sep 14 '11 at 18:29

1 Answers1

2

If, as it appears, you are seeking to get first rows 1-100, then rows 101-200, and so on, then you can use a more direct (but non-standard) syntax. Other DBMS have analogous notations, handled somewhat differently.

To fetch rows 101-200:

SELECT SKIP 100 FIRST 100 t.*
  FROM Table AS T
 WHERE ...other criteria...

You can use a host variable in place of either literal 100 (or a single prepared statement with different values for the placeholders on different iterations).

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
  • Jonathan, it helped me, but didn't work for Informix 9.40, do you know how to do for this version? – Guilherme Oliveira Sep 20 '11 at 12:38
  • If the SKIP/FIRST notation isn't in 9.40, then the fix is to upgrade to a current, supported version (11.70, for example). There really isn't an easy workaround in general SQL. In ESQL/C, you could use a SCROLL CURSOR and then `FETCH ABSOLUTE 101` followed by 99 more `FETCH ` (aka `FETCH NEXT`) operations. – Jonathan Leffler Sep 20 '11 at 14:05
  • I'm assuming SKIP/FIRST directives are only available in IDS and not SE? – Joe R. Sep 25 '11 at 20:27
  • Yes, Frank. There have been no new features in SE for about 15 years now. – Jonathan Leffler Sep 25 '11 at 20:35