RE: SQL Select... the last 10 records from a table ?

2015-09-23 Thread John Dixon 
thanks Bob ... :-)


> From: bobsnei...@iotecdigital.com
> To: use-livecode@lists.runrev.com
> Subject: Re: SQL Select... the last 10 records from a table ?
> Date: Mon, 21 Sep 2015 15:42:05 +
> 
> Inverse sort, limit 10
> 
> Bob S
> 
> 
> > On Sep 20, 2015, at 05:59 , John Dixon <dixo...@hotmail.co.uk> wrote:
> > 
> > I understand how to select, say the top 10 records in a table... What would 
> > the best way to select the last 10 records from a table ?
> >   
> > ___
> > use-livecode mailing list
> > use-livecode@lists.runrev.com
> > Please visit this url to subscribe, unsubscribe and manage your 
> > subscription preferences:
> > http://lists.runrev.com/mailman/listinfo/use-livecode
> 
> 
> ___
> use-livecode mailing list
> use-livecode@lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription 
> preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
  
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: SQL Select... the last 10 records from a table ?

2015-09-21 Thread Mark Schonewille 
Because my solution isn't entirely satisfactory IMHO, I have asked a 
question on Stackoverflow.


http://stackoverflow.com/questions/32705801/select-last-10-records-from-a-table-without-ordering-the-table

--
Mark Schonewille
http://economy-x-talk.com

Buy the most extensive book on the
LiveCode language:
http://livecodebeginner.economy-x-talk.com

Op 9/22/2015 om 00:23 schreef Mark Schonewille:

Hi John,

If you have a date, timestamp or ID number in your table, you can use 
the following query.


SELECT * FROM main ORDER BY date DESC LIMIT 10

Replace date with the column containing the date, timestamp or ID 
number. If the date field hasn't been defined as a date, you may need 
to wrap it in the DATE() function.


If you don't have anything like a date, timestamp or ID number and 
still want to get the last 10 records, in the order they arrived in 
the database, you could use the following query:


SELECT * FROM (SELECT main.*,@rownum:=@rownum+1 AS rank,0 AS c1 FROM 
main,(SELECT @rownum:=0) AS t2) AS t3 WHERE rank > (SELECT COUNT(*) 
FROM main) - 10


Replace "main" with the name of your table.

This part

(SELECT main.*,@rownum:=@rownum+1 AS rank,0 AS c1 FROM main,(SELECT 
@rownum:=0) AS t2) AS t3


creates a temporary table, which includes the entire table main, the 
field 'rank' and an additional field containing the value 0.


The remaining part

SELECT * FROM ( . . . ) AS t3 WHERE rank >  (SELECT COUNT(*) FROM 
main) - 10


selects the records where the rank > the total number of records - 10.

Two side-notes on this:

1) I admit that I'm not entirely sure what I'm doing here. The 
subquery 0 AS C1 FROM main is a bit unexpected and there should be a 
better way to do this, but replacing 0 with * is unnecessary while 
removing this subquery causes an error.


2) This query is very inefficient and slow and may cause an overflow 
if you have a very large database, or perhaps even if you have 
relatively small database. You really should use timestamps and do an 
ordered query with the LIMIT clause.


--
Mark Schonewille
http://economy-x-talk.com

Buy the most extensive book on the
LiveCode language:
http://livecodebeginner.economy-x-talk.com

Op 9/20/2015 om 15:39 schreef John Dixon:

Thanks.. :-) I'll try that ...


Date: Sun, 20 Sep 2015 09:27:44 -0400
Subject: Re: SQL Select... the last 10 records from a table ?
From: roger.e.el...@sealedair.com
To: use-livecode@lists.runrev.com

Sort descending, and still SELECT TOP 10.
On Sep 20, 2015 8:59 AM, "John Dixon" <dixo...@hotmail.co.uk> wrote:


I understand how to select, say the top 10 records in a table... What
would the best way to select the last 10 records from a table ?

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your
subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your 
subscription preferences:

http://lists.runrev.com/mailman/listinfo/use-livecode


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your 
subscription preferences:

http://lists.runrev.com/mailman/listinfo/use-livecode



___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your 
subscription preferences:

http://lists.runrev.com/mailman/listinfo/use-livecode



___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: SQL Select... the last 10 records from a table ?

2015-09-21 Thread Mark Schonewille 

Hi John,

If you have a date, timestamp or ID number in your table, you can use 
the following query.


SELECT * FROM main ORDER BY date DESC LIMIT 10

Replace date with the column containing the date, timestamp or ID 
number. If the date field hasn't been defined as a date, you may need to 
wrap it in the DATE() function.


If you don't have anything like a date, timestamp or ID number and still 
want to get the last 10 records, in the order they arrived in the 
database, you could use the following query:


SELECT * FROM (SELECT main.*,@rownum:=@rownum+1 AS rank,0 AS c1 FROM 
main,(SELECT @rownum:=0) AS t2) AS t3 WHERE rank >  (SELECT COUNT(*) 
FROM main) - 10


Replace "main" with the name of your table.

This part

(SELECT main.*,@rownum:=@rownum+1 AS rank,0 AS c1 FROM main,(SELECT 
@rownum:=0) AS t2) AS t3


creates a temporary table, which includes the entire table main, the 
field 'rank' and an additional field containing the value 0.


The remaining part

SELECT * FROM ( . . . ) AS t3 WHERE rank >  (SELECT COUNT(*) FROM main) - 10

selects the records where the rank > the total number of records - 10.

Two side-notes on this:

1) I admit that I'm not entirely sure what I'm doing here. The subquery 
0 AS C1 FROM main is a bit unexpected and there should be a better way 
to do this, but replacing 0 with * is unnecessary while removing this 
subquery causes an error.


2) This query is very inefficient and slow and may cause an overflow if 
you have a very large database, or perhaps even if you have relatively 
small database. You really should use timestamps and do an ordered query 
with the LIMIT clause.


--
Mark Schonewille
http://economy-x-talk.com

Buy the most extensive book on the
LiveCode language:
http://livecodebeginner.economy-x-talk.com

Op 9/20/2015 om 15:39 schreef John Dixon:

Thanks.. :-) I'll try that ...


Date: Sun, 20 Sep 2015 09:27:44 -0400
Subject: Re: SQL Select... the last 10 records from a table ?
From: roger.e.el...@sealedair.com
To: use-livecode@lists.runrev.com

Sort descending, and still SELECT TOP 10.
On Sep 20, 2015 8:59 AM, "John Dixon" <dixo...@hotmail.co.uk> wrote:


I understand how to select, say the top 10 records in a table... What
would the best way to select the last 10 records from a table ?

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your
subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode



___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: SQL Select... the last 10 records from a table ?

2015-09-21 Thread Bob Sneidar 
Inverse sort, limit 10

Bob S


> On Sep 20, 2015, at 05:59 , John Dixon  wrote:
> 
> I understand how to select, say the top 10 records in a table... What would 
> the best way to select the last 10 records from a table ?
> 
> ___
> use-livecode mailing list
> use-livecode@lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription 
> preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: SQL Select... the last 10 records from a table ?

2015-09-20 Thread Roger Eller 
Sort descending, and still SELECT TOP 10.
On Sep 20, 2015 8:59 AM, "John Dixon"  wrote:

> I understand how to select, say the top 10 records in a table... What
> would the best way to select the last 10 records from a table ?
>
> ___
> use-livecode mailing list
> use-livecode@lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

RE: SQL Select... the last 10 records from a table ?

2015-09-20 Thread Roger Eller 
My SQL buddy also adds, "It all depends on what the SELECT is being
ORDER'ed BY; typically a date time stamp.".
On Sep 20, 2015 9:39 AM, "John Dixon" <dixo...@hotmail.co.uk> wrote:

> Thanks.. :-) I'll try that ...
>
> > Date: Sun, 20 Sep 2015 09:27:44 -0400
> > Subject: Re: SQL Select... the last 10 records from a table ?
> > From: roger.e.el...@sealedair.com
> > To: use-livecode@lists.runrev.com
> >
> > Sort descending, and still SELECT TOP 10.
> > On Sep 20, 2015 8:59 AM, "John Dixon" <dixo...@hotmail.co.uk> wrote:
> >
> > > I understand how to select, say the top 10 records in a table... What
> > > would the best way to select the last 10 records from a table ?
> > >
> > > ___
> > > use-livecode mailing list
> > > use-livecode@lists.runrev.com
> > > Please visit this url to subscribe, unsubscribe and manage your
> > > subscription preferences:
> > > http://lists.runrev.com/mailman/listinfo/use-livecode
> > >
> > ___
> > use-livecode mailing list
> > use-livecode@lists.runrev.com
> > Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> > http://lists.runrev.com/mailman/listinfo/use-livecode
>
> ___
> use-livecode mailing list
> use-livecode@lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

RE: SQL Select... the last 10 records from a table ?

2015-09-20 Thread John Dixon 
Thanks.. :-) I'll try that ...

> Date: Sun, 20 Sep 2015 09:27:44 -0400
> Subject: Re: SQL Select... the last 10 records from a table ?
> From: roger.e.el...@sealedair.com
> To: use-livecode@lists.runrev.com
> 
> Sort descending, and still SELECT TOP 10.
> On Sep 20, 2015 8:59 AM, "John Dixon" <dixo...@hotmail.co.uk> wrote:
> 
> > I understand how to select, say the top 10 records in a table... What
> > would the best way to select the last 10 records from a table ?
> >
> > ___
> > use-livecode mailing list
> > use-livecode@lists.runrev.com
> > Please visit this url to subscribe, unsubscribe and manage your
> > subscription preferences:
> > http://lists.runrev.com/mailman/listinfo/use-livecode
> >
> ___
> use-livecode mailing list
> use-livecode@lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription 
> preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
  
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode