Re: [sqlite] help with sqlite command
Dennis Cote wrote: 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; If the table rows are large, or if the number of rows is large, you might want to do this refinement: create temp_table as select rowid as source_rowid from my_table WHERE ...; select * from my_table, temp_table where temp_table.rowid%N=0 and source_rowid=my_table.rowid; drop table temp_table; Actually, this looks like a great way to implement many kinds of weird sorting/indexing schemes (percentile ranking, hi/low ordering, grouping). Such a case would be to find the decile rankings of an table (with numbers in it). In that case, N would be the count(*) / 10. and the original WHERE would describe the order over which the ranking is to be done. Or use count/2 to get at the median. (if N < 100, one might also need to interpolate).
Re: [sqlite] help with sqlite command
On 3/27/06, Uma Venkataraman <[EMAIL PROTECTED]> wrote: > 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? http://sqlite.org/lang_delete.html
Re: [sqlite] help with sqlite command
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
Thanks Dennis..that seems to do the trick... - Original Message - From: "Dennis Cote" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> 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
Too bad sqlite doesn't have Oracle's ROWNUM: "Pseudo-Columns While not actual datatypes, Oracle supports several special-purpose data elements. These elements are not actually contained in a table, but are available for use in SQL statements as though they were part of the table. ROWNUM For each row of data returned by a SQL query, ROWNUM will contain a number indicating the order in which the row was retrieved. For example, the first row retrieved will have a ROWNUM of 1, the second row will have a ROWNUM of 2, and so on. This approach can be useful for limiting the number of rows returned by a query. To display only ten rows of the emp table, the following SQL statement makes use of the ROWNUM pseudo-column: SELECT * FROM emp WHERE ROWNUM < 11; WARNING: ROWNUM returns a number indicating the order in which the row was retrieved from the table, but this is not always the order in which a row is displayed. For example, if a SQL statement includes an ORDER BY clause, rows will not be displayed in ROWNUM sequence, since ROWNUM is assigned before the sort operation. "
Re: [sqlite] help with sqlite command
On 3/27/06, Dennis Cote <[EMAIL PROTECTED]> wrote: > Jay, > > The rowid is the key from the btree used to store the table rows. It is > not generated dynamically. Ah. Thanks! Learn something new every day.
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" <[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
Re: [sqlite] help with sqlite command
I think select * from mytable where rowid %5 = 0; will get you something like every fifth row in the table. But that assumes your rowids are 1-nnn with no gaps. If your rowids happen to skip a value evenly divisible by 5, you won't get another row until the next one divisible by 5. -Clark - Original Message From: Uma Venkataraman <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Monday, March 27, 2006 11:07:18 AM Subject: Re: [sqlite] help with sqlite command 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.
Re: [sqlite] help with sqlite command
> 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? Sorry, I'm a little off today and wrote the wrong formula! To get the even numbered records use: where rowid % 2 = 0 To get the odd numbered records use: where rowid % 2 = 1 To get every 5th record where rowid % 5 = 0 ( this will return record 5, 10, 15, etc). look up the 'modulus' or 'modulo' operator to see what this does. I believe rowid is assigned dynamically to the result set so it would give a different set of results for a different query. If you want the same records from different select statements you could create an integer column with a primary key and use that instead of rowid.
Re: [sqlite] help with sqlite command
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.
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
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
