MySQL Simple Query Help

Anon

hello - I am trying to do what I'm sure is a relatively simple query on my db but am having trouble with my syntax - this is my best explanation of what I want to achieve (obviously not valid SQL!!). .

select * from table
order by rowID asc
limit LAST 20

I have table with a primary key of an auto-incremeting number - i wish to return the last 20 results in ascending order . . .curretnly I have tried. . .

select * from table order by rowID asc limit 20;

it returns the first 20 rows in the table ordered correctly - i have also tried. . .

create temporary table tmp
select from table order by rowID asc;
select from tmp limit 20;

but that didn;t seem quite right and looked very ugly. . . . .

your time and help much apprecitated.

[deleted]

Don't you mean:

select * from table
order by rowID DESC
limit 20;

A forum, a FAQ, email notification, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

Anon

see - I can't even explain what I want to do in English, no wonder I can't in SQL!

thanks for your reply but you rquery returns the first twenty rows of the table I want the last twenty rows i.e the highest rowID and 19 previous rows?!!

Anon

this does what I want but surely it can be written in one nice line??

create temporary table tmp
select * from table
order by row_id desc;

create temporary table tmpp
select * from tmp limit 20";

select * from tmpp
order by row_id asc";

drop table tmp;
drop table tmpp;

thanks.

[deleted]

"I want the last twenty rows i.e the highest rowID and 19 previous rows?!!"

That is what my query returns.

I guess you mean you want to get the highest 20 rowids, but sorted so that you get the lowest rowid of those 20 first, and ending with the highest.

Simple solution: use my query (which gives you the records you need but in reverse order) and either simply fetch the rows in reverse order, or load them into an array and reverse the array.

A forum, a FAQ, email notification, what else do you need?