Query to select specific rows

[kristolcrawley]

I have a table with 4000 rows. I need to get each 25th row and insert it into a new temp table. How do I get each 25th row using an informix sql statement??

 

[olded]

Kristol:

You can do it but it's not nice. Given a test table:


create table edstmp
(
bumdata integer
);

bumdata is just some integer value for this test:

1) create a temp table, b, which has a serial column and a row_id column.
2) create a serial column for each record in the table.
3) delete every row in the temp table that's not the 25 row - mod of cols
4) display every entry in edstmp from the rowid stored in temp b. (Obviously, your could use another primary key other than rowid).

create temp table b (
cols serial(1),
row_id integer
) with no log;
insert into b
select 0, rowid from edstmp;
delete from b where mod(cols,25) != 0;
select * from edstmp where rowid in (select row_id from b);

Maybe somebody smarter than I can come up with a better solution.

Regards,


Ed

 

[dodge20]

This is how you do it in oracle. I don't know if this works in Informix, but you can give it a try, maybe olded can help modify if you need to.

insert into t1
select st_cd, st_dscrptn from
(select st_cd, st_dscrptn, row_number() over (order by st_cd) rn from state)
where mod(rn,25) = 0;
Dodge20
If it ain't broke, don't fix it.

 

[olded]

Hi:

I'm not an oracle person, but it looks like the row_number function is similar to the informix rowid (informix recommends not using it because of portability issues). Unfortunately, the rowid is guaranteed only to be unique - not to be in any particular order or any particular value.

Dodge and I are thinking along the same lines, but I decided to create a tmp table with a serial field which by default starts at 1 and increments by 1 as records add.

Regards,

Ed