DELETE statement doesn't support ORDER BY clause and by default doesn't support LIMIT clause. So you should do the select, find the value of prty for the second row and then issue a delete adding to its WHERE clause "and prty < ?" where you will bind that value of prty you found.
Pavel On Mon, Nov 21, 2011 at 3:32 PM, Don V Nielsen <[email protected]> wrote: > I'm confused by the error message, "near 'order' : syntax error", with the > following statement. The same statement, beginning with "select *" in > place of "delete", will function fine. > > delete from seg_ny_adds > where needid = 90 > order by prty desc > limit (select count() from seg_ny_adds where needid = 90) - 2 > > What I am trying to accomplish is to remove from each group (needid > identifies the group) all but the top 2 rows. > The plan is to loop through the table, identify each group that has more > than two rows, order them by priority descending, > and then delete the top records leaving the bottom two. The bottom two > will be the highest priority rows. > > Thanks for time and consideration, > dvn > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
