How to select only 10 last rows in a table?

[Vladimir Makarov]

So, I need to select data from the table but the only ten last rows (with highest id).

I have already tried with select all records and then use while statement but I think there is another way.

Any ideas? Thanks!

[Massimo Di Pierro]

if you have a SQL db then you can order them by reverse id

db(....).select(orderby=~db.tablename.id,limitby=(0,10))

otherwise you should have some timestamp field (as created_on in auth.signature) and use that field.

[Annet]

Hi Vladimir,

Some time ago Anthony provided me with the following solution:

maxID=db(db.node).select(db.node.id.max()).first()['MAX(node.id)']
rows=db(db.node.id>=maxID-10).select(db.node.id,db.node.computedName,orderby=~db.node.id)


Kind regards,

Annet

[Massimo Di Pierro]

Instead of this

maxID=db(db.node).select(db.node.id.max()).first()['MAX(node.id)']

I would do

maxID=db(db.node).select(db.node.id.max()).first()[db.node.id.max()]

to make sure it will continue work in the future. The former is implementation dependent.

[Anthony]

On Wednesday, September 12, 2012 12:35:41 PM UTC-4, Massimo Di Pierro wrote:

Instead of this

maxID=db(db.node).select(db.node.id.max()).first()['MAX(node.id)']

I would do

maxID=db(db.node).select(db.node.id.max()).first()[db.node.id.max()]

Well, that's what I really recommended: https://groups.google.com/d/msg/web2py/VY71mF2cl-4/oNE00OduXQ0J. :-)

 

[Niphlod]

ps: methods are NOT equivalent. They are if you have "continous" ids.
But, e.g., you remove some rows. You end up with 1,2,3,4,5,6,7,8,9,10,11,13,15,17,20.
Second method (i.e. calc max and go back by ten) leaves you with 20,17,15,13,11,10 (and takes two queries)
First method (i.e. orderby + limitby) correctly returns 20,17,15,13,11,10,9,8,7,6.

[Anthony]

Yes, the "max" solution was originally for a different problem that only needed the single max value and involved only one query. In this case, the orderby/limitby solution is the way to go.

Anthony

[Massimo Di Pierro]

On a second look.... this assumes that no record was deleted. It does not always select the last 10 records.

On Wednesday, 12 September 2012 11:29:04 UTC-5, Annet wrote:

[Niphlod]

@anthony, massimo: I surely trust that you know what are you doing.
My reply was only a specification for "future references" as the title of the post could be found by some users and may come handy.

[Anthony]

Yes, I'm glad you pointed it out.

[Vladimir Makarov]

Thank you all for your hints!!!

The easiest way, of course, is to use orderby and limitby. It works fine.

But the MAX method is usefull too. I'll use it in my projects.