Re: [sqlite] X most recent entries

2011-03-14 Thread Pavel Ivanov 
Try this one:

select * from (select * from multiturnTable order by rowid desc limit 5000)
where (player1 = ? or player2 = ?)
and (complete=0 or p1SubmitScore=0 or p2SubmitScore=0)


Pavel


On Mon, Mar 14, 2011 at 1:58 PM, Ian Hardingham  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 > > 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 
>>     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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] X most recent entries

2011-03-14 Thread Adam DeVita 
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"  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  > > 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 
> > 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

Re: [sqlite] X most recent entries

2011-03-14 Thread Duquette, William H (318K) 
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"  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  > 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 
> 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

--
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

Re: [sqlite] X most recent entries

2011-03-14 Thread Ian Hardingham 
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  > 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 
> 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

Re: [sqlite] X most recent entries

2011-03-14 Thread Adam DeVita 
select id from table order by id desc limit 5000


Adam

On Mon, Mar 14, 2011 at 1:52 PM, Ian Hardingham  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
> 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