[mysql] Select specific row from mysql table

Ideally I need a query that is equivalent to

select * from customer where row_number() = 3

but that's illegal.

I can't use an auto incremented field.

row_number() is the row that needs to be selected.

How do I go about this?

EDIT: Well, I use iSql*plus to practice, and using limit and auto_increment is illegal for some reason. I ended up creating a sequence and a trigger and just upped the id by 1 every time there was an entry.

You cannot select a row like that. You have to specify a field whose values will be 3

Here is a query that will work, if the field you are comparing against is id

select * from customer where `id` = 3

You can add an auto generated id field in the table and select by this id

SELECT * FROM CUSTOMER WHERE CUSTOMER_ID = 3;

SET @customerID=0;
SELECT @customerID:=@customerID+1 AS customerID
FROM CUSTOMER ;

you can obtain the dataset from SQL like this and populate it into a java data structure (like a List) and then make the necessary sorting over there. (maybe with the help of a comparable interface)

SQL tables are not ordered by default, and asking for the n-th row from a non ordered set of rows has no meaning as it could potentially return a different row each time unless you specify an ORDER BY:

select * from customer order by id where row_number() = 3

(sometimes MySQL tables are shown with an internal order but you cannot rely on this behaviour). Then you can use LIMIT offset, row_count, with a 0-based offset so row number 3 becomes offset 2:

select * from customer order by id
limit 2, 1

or you can use LIMIT row_count OFFSET offset:

select * from customer order by id
limit 1 offset 2

Your table will need to be created with a unique ID field that will ideally have the AUTO_INCREMENT attribute. example:

CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
PRIMARY KEY (P_Id)
)

Then you can access the 3rd record in this table with:

SELECT * FROM Persons WHERE P_Id = 3