MySQL newbie question: how do I select a row by row number?

I’m new to Mysql and hope this question won’t irritate too many people.
I wanna select a row by its row number. I was wondering if there’s any function that can do that. Now my table has a column call “id” which was set to autoincrement. “id” can distinguish each row but it can’t represent the row number since when we delete one row, “id” won’t decrement automatically. So do I need to add another collumn called “rownum” to specify the row number manually?
Thanks.

Since you’re using MySQL, you can do this:


SELECT *
FROM your_table
ORDER BY id DESC
LIMIT row_num, 1

where row_num is the no. of the row you want minus 1

Thanks a lot,man. That helps. Can you explain a little bit more about LIMIT as I can’t really find information about how to use it.

joel, are you assuming row_num is an actual column? variable? literal number pre-calculated before the query is run? i looked in the mysql docs and couldn’t find anything about a row number

xiaohai, may i ask why you need a row number? just curious

rudy

Rudy,
I don’t think Joel is assuming a new column. I’ve tried it and it worked just as I wanted without adding a new column. Maybe I’m going the long way, but what I’m trying to do is to display the whole table in a series pages instead of one. For example, I need to display a table of 100 rows in 10 pages. Then I need the row numbers to pull the rows out.

ah, paged output, now i get it

just curious, but did you run it with row_num or with an actual number?

Nope row_num is a variable that’s probably going to come from the programming language used. I am assuming though that “row number” means the position of the row, sorted by it’s id (meaning smallest id is 1st row, next smallest is 2nd).

thanks, it is as i had suspected – by the time mysql sees it, it’s a numeric literal

You don’t neet a row_number pseudo-column to accomplish this. The original auto_increment will do just fine, with deletes and all. LIMIT is an offset but is not calculated by taking start_id + limit_value. Try it, you’ll be fine if you simply keep track of what ID you last ended at.

how do I delete rows using LIMIT? say, i wanna delete from row 5 to row 10 order by id DESC?