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

[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.*, at 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.*, at 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.eller at sealedair.com
>> To: use-livecode at lists.runrev.com
>>
>> Sort descending, and still SELECT TOP 10.
>> On Sep 20, 2015 8:59 AM, "John Dixon" <dixonja at 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 at 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 at 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 at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode