mysql row counter

I have a mysql table. It has auto increment on the id. but I regularly delete rows so the numbers are all over the place. I need to get the last n rows out, but because of deletions, the common way of using the max of the autoincremented id column doesn't work well...

1 - Is their another way to get the bottom 50?

2 - Is their a way to get rows by actual row number? so if I have 4 rows labelled 1,2,3,4 delete row 2 then it will become开发者_开发百科 1,2,3 rather than 1,3,4?

SELECT ... ORDER BY id DESC LIMIT 50

SELECT *
  FROM TABLE
 ORDER BY id DESC
 LIMIT 50

EDIT

To pick the last 50, but sort by id ASC

SELECT X.*
  FROM ( SELECT *
           FROM TABLE
          ORDER BY id DESC
          LIMIT 50
       ) X
 ORDER BY X.id

1 - First get total row count like

SELECT COUNT(*) AS c FROM ...

then use

SELECT ..... LIMIT [start],[count]

2 - One idea is to use view , or procedure, but this is much more harder and may be used when there is no other way to avoid this

1 - Is their another way to get the bottom 50?

SELECT * FROM table_name ORDER BY record_id DESC LIMIT 50

2 - Is their a way to get rows by actual row number? so if I have 4 rows labelled 1,2,3,4 delete row 2 then it will become 1,2,3 rather than 1,3,4?

SELECT * FROM table_name

1 - Yes but it is ugly afaik, you do a

SELECT whateveryouwant FROM table ORDER BY yourprimarykey DESC LIMIT 50

the you fetch the rows into an array and reverse the array, in php :

$r = mysql_query('SELECT * FROM table ORDER BY primarykey DESC LIMIT 50');
$set = array();
while($row = mysql_fetch_assoc($r)) $set = $row;
$set = array_reverse($set);
foreach($set as $row) {
  // display row ...
}

2 - You'll have to manage your primary key by yourself, its a bit risky ...