Row number where the name is located

Question

Using PHP/MySQL. How can I get the number of row or position of the row where the name is located? Example

 ________________  Row
|__ name_|_ age _|  1
| Joe    |  30   |  2
| Henry  |  24   |  3
| Rick   |  55   |  4
| Tom    |  19   |  5

There is no row number in the Database So I want a code through which I can use their name and get the position of the row. Like if the name is Tom - it'll echo that tom is located in row 5. Thank you!

Order is not a component of a table. When run over time Tom may be 5 on one run 3 on another and 10 later. You can have the system GENERATE a row number for each query run by using user variables; but as previously stated, the order is not guaranteed so row# may vary over time. `SELECT A.*, @rn:=@rn+1 as Row from TableName A cross join (Select @rn :=0) B order by Name` — xQbert, Dec 09 '16 at 16:48
Rows represent unordered sets. So there is no such thing as 'row number'. So, in what sense is Tom in row 5? — Strawberry, Dec 09 '16 at 16:52
@aynber how do I do that ? I use PHPmyadmin for maintaining my DB. — Devil Rooter, Dec 09 '16 at 16:56
There's an option to add a column, below the table. Add a column named `id`, set it to `integer` and `auto_increment`. — aynber, Dec 09 '16 at 16:57
@Strawberry This is added while registering. They are all in order by the date they registered. Like the one who registered at first is in first place. — Devil Rooter, Dec 09 '16 at 16:57
@DevilRooter http://stackoverflow.com/questions/7661181/how-to-add-auto-increment-primary-key-based-on-an-order-of-column shows how to add an incremental ID per aynber's suggestion — xQbert, Dec 09 '16 at 17:04
@Strawberry but everything in DB is aligned according to the time they registered. — Devil Rooter, Dec 09 '16 at 17:06
No. That's just a coincidence and cannot be relied upon. If you had indexes and/or a PRMARY KEY (a requirement of a relational table) this would affect the order in which rows were returned. — Strawberry, Dec 09 '16 at 17:09
Im sorry. I had no idea about these things. Ill try auto_increment thing! — Devil Rooter, Dec 09 '16 at 17:12
If there's an auto-incrementing key (or a date - assuming only one individual can be registered at a time), then the problem becomes trivial. But the comments section isn't the place to go into detail on that. If you have a new question, either ask a new question, or edit your existing one. — Strawberry, Dec 09 '16 at 17:16

xQbert · Answer 1 · 2016-12-09T18:16:40.503

Using user variables you can simulate the row_number function.

Keep in mind the # assigned to each person could vary each time the query is run. Ordering by dateRegistered instead of name could help but if any insert, update or delete occurs which adds a new record, deletes one, or changes the dateRegistered to a time past, could change this numbering.

SELECT A.*, @rn := @rn+1 as Row
FROM aTable A 
CROSS JOIN (Select @rn :=0) B 
ORDER BY Name

Working SQL fiddle

Now if you want a permanent ID... be sure to alter the update to change the order by to your date field if you want the ID's to be put in the order of the date, but it really shouldn't matter as a PK generated in such fashion (Surrogate key) has no meaning other than uniquely identify the record.

create table atable (
   name varchar(10),
  age int);

  Insert into atable values
  ('Joe',30),
  ('Henry',24),
  ('Rick',55),
  ('Tom',19);


ALTER table atable add column ID int;

Update atable 
INNER JOIN (
  SELECT A.*, @rn := @rn+1 as Row
  FROM aTable A 
  CROSS JOIN (Select @rn :=0) B 
  ORDER BY Name) B
  on atable.Name = B.Name
 and atable.Age = B.Age
set atable.id = B.Row;

ALTER TABLE atable MODIFY ID int NOT NULL primary key AUTO_INCREMENT;

Working fiddle

@Strawberry I accept the down vote; but it works fine in simulating row number. 5 for Tom was an example and I indicated this number can change on each run (it's unclear if this needs to be a constant). I would also point out that if this value needs to be consistent each run, the link I added at the top based on aynber's comment, shows how to do this. — xQbert, Dec 09 '16 at 17:06
Only if you assume they need the ID to be consistent on each run. That's not been specified. — xQbert, Dec 09 '16 at 17:20

Row number where the name is located

1 Answers1