[sqlite] sql question

[Uma Venkataraman]

I want to recycle the table for which I need to be able to delete the first 
100 records from a table and add 100 new records.


Thanks

Re: [sqlite] help with sqlite command

[Uma Venkataraman]

I would like to delete n records from a table, based on some condition. Can 
some one please let me know how to do this with sqlite?


Thanks 

Re: [sqlite] help with sqlite command

[Uma Venkataraman]

Thanks Dennis..that seems to do the trick...

- Original Message - 
From: "Dennis Cote" <[EMAIL PROTECTED]>

To: 
Sent: Monday, March 27, 2006 2:46 PM
Subject: Re: [sqlite] help with sqlite command



Jay Sprenkle wrote:



I believe rowid is assigned dynamically to the result set so it would
give a different
set of results for a different query.


 


Jay,

The rowid is the key from the btree used to store the table rows. It is 
not generated dynamically.


To get every N'th row after deletions you need some way to assign a 
series of integers to the result rows. The easiest way I can think of is 
to create a temporary table from your initial query. Then you can use 
the modulus operator to select every N'th record from that table as you 
have suggested since the rowids will all be freshly assigned. You will 
also need to drop the temp table when you are done with it.


 create temp table temp_table as select * from my_table where ;
 select * from temp_table where rowid % N = 0;
 drop table temp_table;

HTH
Dennis Cote

Re: [sqlite] help with sqlite command

[Uma Venkataraman]

Hi Jay,

Thanks for your reply. I am trying the command

   select * from mytable where row_id = row_id % 5

from sqlite browser and it says, no such column row_id.. Also I replaced 
row_id with rowid and it gave only the first 4 records from my table. My 
other concern is I will be deleting and adding records to the table. If I 
want to select every nth record after such deletions and additions will the 
row id not get affected?


Thanks
Uma



- Original Message - 
From: "Jay Sprenkle" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Monday, March 27, 2006 1:56 PM
Subject: Re: [sqlite] help with sqlite command


On 3/27/06, Uma Venkataraman <[EMAIL PROTECTED]> wrote:

Hi All,

I need to be able to select  the TOP N rows from a table. How do i do it =


select * from mytable limit 5



with sqlite? Also  how does one select EVERY Nth row from a table?


use modulus operator for that:
select * from mytable where row_id = row_id % 5



---
On Wednesday, March 1, 2006, at a hearing on the proposed
Constitutional Amendment to prohibit gay marriage, Jamie Raskin,
professor of law at AU, was requested to testify.

At the end of his testimony, Republican Senator Nancy Jacobs said:
"Mr. Raskin, my Bible says marriage is only between a man and a woman.
What do you have to say about that?"

Raskin replied: "Senator, when you took your oath of office, you
placed your hand on the Bible and swore to uphold the Constitution.
You did not place your hand on the Constitution and swear to uphold
the Bible."

The room erupted into applause. 

[sqlite] help with sqlite command

[Uma Venkataraman]

Hi All,

I need to be able to select  the TOP N rows from a table. How do i do it =
with sqlite? Also  how does one select EVERY Nth row from a table?

Thanks