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.

What do you mean by `row_number()`? Records are not ordered in MySQL. You need to identify your desired data some other way, based on their content. — eggyal, May 04 '12 at 23:43
@eggyal: Standard ANSI SQL does have a `row_number()` function (although Marlon's usage would be incorrect) but MySQL does not support that (as so many other modern SQL features) — , May 04 '12 at 23:46

sp00m · Accepted Answer · 2015-07-06T07:15:54.320

109

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

edited Jul 06 '15 at 07:15

answered May 04 '12 at 23:47

sp00m

47,968
31
142
252

1

What does LIMIT have to do with this problem? – Starx May 04 '12 at 23:49
3

@Starx If @MarlonBrando wants simply the third row of his result, not regarding any technical ID, `LIMIT` is the solution. – sp00m May 04 '12 at 23:52
this actually works, and is indeed the solution to the problem yet ;) – nawfal May 04 '12 at 23:53
1

@nawfal, How will `row_number() = 3 LIMIT 3,1` return a valid result? – Starx May 04 '12 at 23:55
2

@Starx Yeah, of course, you have to remove the `WHERE row_number() = 3` of your query... – sp00m May 04 '12 at 23:57
1

This worked great for me. I have a quiz database and I want to display each row of the table on a separate page with the question and answers. A next button keeps track of the page number. My PHP/MySql Limit statement is: LIMIT $pageNumber, 1 – JScarry Oct 17 '13 at 20:59

score 8 · Answer 2 · answered May 04 '12 at 23:48

8

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

answered May 04 '12 at 23:48

Starx

77,474
47
185
261

score 6 · Answer 3 · answered May 04 '12 at 23:54

6

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)

answered May 04 '12 at 23:54

Sanath

4,774
10
51
81

1

this approach is highly inefficient for large datasets – Joaquín L. Robles Apr 13 '23 at 13:54

score 2 · Answer 4 · answered Jun 22 '17 at 05:00

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

score 1 · Answer 5 · answered May 04 '12 at 23:45

1

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

SELECT * FROM CUSTOMER WHERE CUSTOMER_ID = 3;

answered May 04 '12 at 23:45

duncanportelli

3,161
8
38
59

score 1 · Answer 6 · answered May 04 '12 at 23:53

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

Select specific row from mysql table

6 Answers6

Linked