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
