CODEWITHSUNDEEP
phpmysql
David19801
David19801

Reputation: 11448

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?

Upvotes: 0

Views: 544

Answers (5)

Mark Baker
Mark Baker

Reputation: 212412

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

Upvotes: 3

yent
yent

Reputation: 1343

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 ...

Upvotes: 0

Phill Pafford
Phill Pafford

Reputation: 85318

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

Upvotes: 0

awm
awm

Reputation: 6570

SELECT ... ORDER BY id DESC LIMIT 50

Upvotes: 5

SergeS
SergeS

Reputation: 11779

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

Upvotes: 0

Related Questions