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

[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.*, 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
>
>
> _______________________________________________
> 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