"Uma Venkataraman" <[EMAIL PROTECTED]> writes:
> Hi Jay,
>
> Thanks for your reply. I am trying the command
>
> select * from mytable where row_id = row_id % 5
Try this instead:
SELECT * FROM mytable WHERE ROWID % 5 = 0;
Note that if you have an integer primary key in mytable, then ROWID and your
primary key are the same thing. If those ROWID values are not incrementing
numbers (e.g. you inserted values into your primary key which were out of
sequence or if you have deleted any rows) then this method won't work.
Here's an example of one way to do it:
SQLite version 3.2.1
Enter ".help" for instructions
sqlite> .read /tmp/x.sql
CREATE TABLE x (i INTEGER PRIMARY KEY, t TEXT);
INSERT INTO x VALUES (1, 'one');
INSERT INTO x VALUES (2, 'two');
INSERT INTO x VALUES (3, 'three');
INSERT INTO x VALUES (4, 'four');
INSERT INTO x VALUES (5, 'five');
INSERT INTO x VALUES (6, 'six');
INSERT INTO x VALUES (7, 'seven');
INSERT INTO x VALUES (8, 'eight');
-- Retrieve every other row (we hope)
SELECT * FROM x WHERE ROWID % 2 = 0;
2|two
4|four
6|six
8|eight
-- Delete a row
DELETE FROM x WHERE i = 4;
-- The table now looks like this:
SELECT * FROM x;
1|one
2|two
3|three
5|five
6|six
7|seven
8|eight
-- Retrieve what should be every other row, but isn't
SELECT * FROM x WHERE ROWID % 2 = 0;
2|two
6|six
8|eight
-- Insert the values into a new table so pk is properly incrementing
CREATE TEMPORARY TABLE y
(pk INTEGER PRIMARY KEY,
i INTEGER,
t TEXT);
INSERT INTO y (i, t) SELECT i, t FROM x;
-- Now we can get every other row
SELECT * FROM y WHERE pk % 2 = 0;
2|2|two
4|5|five
6|7|seven
DROP TABLE y;
sqlite>
Cheers,
Derrell
