Re: [sqlite] Help on DELETE FROM...

2011-01-18 Thread Marcus Grimm
ite-users-boun...@sqlite.org on behalf of Marcus Grimm > Sent: Mon 1/17/2011 11:18 AM > To: General Discussion of SQLite Database > Subject: EXTERNAL:Re: [sqlite] Help on DELETE FROM... > > > > > On 17.01.2011 17:26, Black, Michael (IS) wrote: >> I came up with the lo

Re: [sqlite] Help on DELETE FROM...

2011-01-17 Thread Black, Michael (IS)
se Subject: EXTERNAL:Re: [sqlite] Help on DELETE FROM... On 17.01.2011 17:26, Black, Michael (IS) wrote: > I came up with the logical opposite which I think does what you want > > select a.id from thi as a where a.id in (select thi.id from thi where > a.userid=thi.userid order by timestamp l

Re: [sqlite] Help on DELETE FROM...

2011-01-17 Thread Marcus Grimm
rom thi where a.userid=thi.userid > ...> order by timestamp limit 100 offset 10); > 10|20|10|0 > 11|21|10|0 > 12|22|10|0 > 23|31|20|0 > 24|32|20|0 > 25|33|20|0 > > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Northrop Grumman

Re: [sqlite] Help on DELETE FROM...

2011-01-17 Thread Igor Tandetnik
Igor Tandetnik wrote: > delete from THI where ID not in ( >select ID from THI t2 where t2.UserID = UserId >order by t2.TimeStamp desc limit 10); Correction: delete from THI where ID not in ( select t2.ID from THI t2 where t2.UserID = THI.UserId order by

Re: [sqlite] Help on DELETE FROM...

2011-01-17 Thread Igor Tandetnik
Black, Michael (IS) wrote: > The "NOT IN" approach doesn't work..here's sample data using select rather > than delete to show the result. > > sqlite> select * from THI where ID not in ( >...> select ID from THI t2 where t2.UserID = UserId >...> order by

Re: [sqlite] Help on DELETE FROM...

2011-01-17 Thread Marcus Grimm
e > Northrop Grumman Information Systems > > > > > From: sqlite-users-boun...@sqlite.org on behalf of Marcus Grimm > Sent: Mon 1/17/2011 10:24 AM > To: General Discussion of SQLite Database > Subject: EXTERNAL:Re: [sqlite] Help on DELETE FROM... >

Re: [sqlite] Help on DELETE FROM...

2011-01-17 Thread Black, Michael (IS)
0|0 24|32|20|0 25|33|20|0 Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Igor Tandetnik Sent: Mon 1/17/2011 10:57 AM To: sqlite-users@sqlite.org Subject: EX

Re: [sqlite] Help on DELETE FROM...

2011-01-17 Thread Igor Tandetnik
Marcus Grimm wrote: >> delete from THI where ID not in ( >> select ID from THI t2 where t2.UserID = UserId >> order by t2.TimeStamp desc limit 10); A correction: I think the subselect should say "select t2.ID from ..." > Thanks Igor! > but a stupid question:

Re: [sqlite] Help on DELETE FROM...

2011-01-17 Thread Black, Michael (IS)
Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Marcus Grimm Sent: Mon 1/17/2011 10:24 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] Help on DELETE

Re: [sqlite] Help on DELETE FROM...

2011-01-17 Thread Marcus Grimm
On 17.01.2011 17:14, Igor Tandetnik wrote: > Marcus Grimm wrote: >> I have a table to record some history data, for example >> items a user recently selected: >> >> CREATE TABLE THI(ID INTEGER PRIMARY KEY, TimeStamp INTEGER, UserID INTEGER, >> DataID INTEGER); >> >>

Re: [sqlite] Help on DELETE FROM...

2011-01-17 Thread Igor Tandetnik
Marcus Grimm wrote: > I have a table to record some history data, for example > items a user recently selected: > > CREATE TABLE THI(ID INTEGER PRIMARY KEY, TimeStamp INTEGER, UserID INTEGER, > DataID INTEGER); > > That table needs to trace only the last 10 events,

Re: [sqlite] Help on DELETE FROM...

2011-01-17 Thread Marcus Grimm
; Advanced Analytics Directorate > Northrop Grumman Information Systems > > > > > From: sqlite-users-boun...@sqlite.org on behalf of Marcus Grimm > Sent: Mon 1/17/2011 6:17 AM > To: General Discussion of SQLite Database > Subject: EXTERNAL:[sqlit

Re: [sqlite] Help on DELETE FROM...

2011-01-17 Thread Black, Michael (IS)
Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Marcus Grimm Sent: Mon 1/17/2011 6:17 AM To: General Discussion of SQLite Database Subject: EXTERNAL:[sqlite] Help on DELETE FROM... Hi List, sorry for not being

Re: [sqlite] Help on DELETE FROM...

2011-01-17 Thread venkat easwar
the Bugs From: Marcus Grimm <mgr...@medcom-online.de> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Mon, January 17, 2011 5:47:45 PM Subject: [sqlite] Help on DELETE FROM... Hi List, sorry for not being very sqlite specifi

[sqlite] Help on DELETE FROM...

2011-01-17 Thread Marcus Grimm
Hi List, sorry for not being very sqlite specific here but I would like to have an advice on a delete operation for which I can't find the right sql command. Currently I do it on C programming level using a loop but I think there must be a better sql way. Anyway, here is the story: I have a