Get records from the end

[gxydas]

Hi all,
my question is how can I get the last 20 rows from a table without changing the order the table is.
I use PHP 5.
Thanks.

 

[r937]

you cannot

the table has no order

only a SELECT has an order, and it is determined by the ORDER BY clause

if you have something like an auto_increment or datestamp column, you can order by that

SELECT foo,bar FROM daTable ORDER BY id DESC LIMIT 20

r937.com | rudy.ca

 

[gxydas]

Thanks r937,

Ok i do that.
The point is that i want to display those 20 records not in DESC order.

 

[DotNetGnat]

the records will be in the descending order of your id or you can also use date...

that means you will get last 20 rows of your table...

for example if you use descending order of the date then you will get recent 20 records...

does that make sense?

-DNG

 

[r937]

and then i assume you want to sort them back into ASC order for display purposes, right?

okay, two comments

1. not sure if can do this with a subquery, seems i recall you can't use LIMIT, but in any case, since you know you are returning exackly 20 rows, you can just reverse display them (in coldfusion you process the query structure backwards, i assume it's equally trivial in other scripting languages like php)

2. it's the last 20, for cryin' out pete's sake, the user will instantly recognize this fact if presented with data in descending sequence, especially if accompanied by a heading like "last 20 thingies", in which case this option -- the ever-loved "do nothing" option which should be included in every situation analysis -- is the best option



r937.com | rudy.ca

 

[gxydas]

Sorry, i think i did not make my self clear.
I want the last 20 records in ASC order.

for example 21st, 22nd, 23rd ... 40th

 

[gxydas]

r937 wrote:

'and then i assume you want to sort them back into ASC order for display purposes, right?'

This is exactly what i want to do but i don't know how!

 

[nego78]

maybe this help

SELECT * FROM (SELECT * FROM tab ORDER BY Id DESC LIMIT 20) AS f ORDER BY f.Id ASC

Via-Net - web directory