CODEWITHSUNDEEP
mysql
Jebathon
Jebathon

Reputation: 4581

Find Row Number of Record in MySQL

Suppose I have a table with one column - called 'person' that contains a list of names. I want to find a specific person based off his index.

I tried using a sql variable to track each column index but the issue is - is that if I have a table of 5 records this will always output the 5th record. 

SET @row_num = 0; SELECT @row_num := @row_num + 1 as row1 ,person FROM table; 
SELECT row1 from table WHERE person = 'name'

Upvotes: 0

Views: 52

Answers (2)

jkeuhlen
jkeuhlen

Reputation: 4517

I would recommend changing your database to add a second column for row_id. This is a fairly common practice. Then you can just use

SELECT * from table WHERE row_id = 3;

This will return the third row.

Upvotes: 1

Rahul
Rahul

Reputation: 77896

Another best possible way would be by means of a TEMPORARY TABLE as explained below

create a temp table

create temporary table temptab(ID INT NOT NULL AUTO_INCREMENT,Person VARCHAR(30))

Then insert data to temp table as

insert into temptab(Person) select Person from mytable

Then select the specific index person name from temp table like

select Person from temptab where ID = 5

Upvotes: 0

Related Questions