CODEWITHSUNDEEP
informix
airliner
airliner

Reputation: 31

Select every n-th row in Informix?

I was wondering if it is possible to select every n-th row in Informix just like in MS SQL?!

Something like

SELECT * FROM <TABLE> order by <COLUMN> ASC limit 1 OFFSET 4

just didn't work. We have to work with driver version 4.10.FC9DE.

My goal is to get only every 5th row back from a table with about 350 entries. I'm happy for every hint to achieve this.

Upvotes: 2

Views: 601

Answers (3)

pmpreciado
pmpreciado

Reputation: 1

Select 1 every 5 rows:

SELECT * FROM <TABLE> WHERE mod(rowid, 5) = 0;

Select 1 every 10 rows:

SELECT * FROM <TABLE> WHERE mod(rowid, 10) = 0;

Upvotes: 0

Antonio J. Ortiz
Antonio J. Ortiz

Reputation: 11

select  skip 4  first 1 *
from <table>
order by <column> asc

You can see more at:

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_0987.htm

Upvotes: 0

Jeroni Chamorro
Jeroni Chamorro

Reputation: 270

I propose this solution to select every 5th row:

First I number all the rows from 1, then select every row that MOD 5 is 0

SELECT t.*
  FROM (SELECT *, SUM(1) OVER (ORDER BY <COLUMN>) AS num
          FROM <TABLE> ) AS t
 WHERE MOD(t.num, 5) = 0

Surely, this is not the most efficient way to make this

Upvotes: 2

Related Questions