Select specific row from mysql table

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can use LIMIT 2 1 instead of WHERE row number     3   As the documentation explains  the first argument specifies the offset of the first row to return  and the second specifies the maximum number of rows to return   Keep in mind that it s an 0-based index  So  if you want the line number n  the first argument should be n-1  The second argument will always be 1  because you just want one row  For example  if you want the line number 56 of a table customer   SELECT   FROM customer LIMIT 55 1

User · Answer

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

User · Answer

You can add an auto generated id field in the table and select by this id  SELECT   FROM CUSTOMER WHERE CUSTOMER ID   3

[mysql] Select specific row from mysql table

Examples related to mysql

Examples related to select

Examples related to row-number