Are you wanting the last 5000 from player 1 and last 5000 from player 2? You can even limit and order the sub selects.
Otherwise, I don't see the purpose of a union when OR would do. SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM multiturnTable WHERE player1 ='?' order by rowid desc limit 5000 UNION ALL SELECT rowid FROM multiturnTable WHERE player2 = '?' rowid desc limit 5000) AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) I'm not sure of your context, but SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM multiturnTable WHERE player1 ='?' AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) order by rowid desc limit 5000 UNION ALL SELECT rowid FROM multiturnTable WHERE player2 = '?' AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) rowid desc limit 5000) AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) so you get the last 5000 qualifying records of each, rather than the latest 5000 of each and then filtering out the disqualifying ones On Mon, Mar 14, 2011 at 2:02 PM, Duquette, William H (318K) < william.h.duque...@jpl.nasa.gov> wrote: > Assuming that higher rowids really are later rowids, wouldn't adding "ORDER > BY rowid DESC" and "LIMIT 5000" do the job? > > Will > > > On 3/14/11 10:58 AM, "Ian Hardingham" <i...@omroth.com> wrote: > > Ah, sorry about this - my query is this one: > > SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM > multiturnTable WHERE player1 ='?' UNION ALL SELECT rowid FROM > multiturnTable WHERE player2 = '?') AND (complete=0 OR p1SubmitScore=0 > OR p2SubmitScore=0) > > And I only want to consider the last 5000 for any SELECTs from > multiturnTable. > > Thanks, > Ian > > On 14/03/2011 17:54, Adam DeVita wrote: > > select id from table order by id desc limit 5000 > > > > > > Adam > > > > On Mon, Mar 14, 2011 at 1:52 PM, Ian Hardingham <i...@omroth.com > > <mailto:i...@omroth.com>> wrote: > > > > Hey guys. > > > > I have a table with an autoincrement primary ID, and as part of a > > select > > I would like to only take the 5000 "largest"/most recent ids. Is > > there > > a quick way of doing this without having to get the max first? > > > > Thanks, > > Ian > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org <mailto:sqlite-users@sqlite.org> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > -- > > VerifEye Technologies Inc. > > <905-948-0015>905-948-0015x245 > > 151 Whitehall Dr, Unit 2 > > Markham ON, L3R 9T1 > > Canada > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- > Will Duquette -- william.h.duque...@jpl.nasa.gov > Athena Development Lead -- Jet Propulsion Laboratory > "It's amazing what you can do with the right tools." > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
