SELECT index of single row in table

Question

I have a MYSQL table which stores teams.

Table structure:
CREATE TABLE teams ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(28) COLLATE utf8_unicode_ci NOT NULL, UNIQUE KEY id (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

Sample data:
INSERT INTOteamsVALUES (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'), (5, 'five');

Use:
SELECT id, name, id as rowNumber FROM teams WHERE id = 4
Returns the correct rowNumber, as there are really three rows infront f it in the table. But this only works as long as I don't remove a row.

Example:
Let's say I DELETE FROM teams WHERE id = 3;
When I now use SELECT id, name, id as rowNumber FROM teams WHERE id = 4 the result is wrong as there are now only two rows (id's 1&2) infront of it in the table.

How can I get the "real" row number/index ordered by id from one specific row?

are you trying to order queries by ID? you can add to the query ORDER BY id. — Ataur Rahim Chowdhury
– Ataur Rahim Chowdhury, Commented Oct 20, 2012 at 15:07

Yogendra Singh · Accepted Answer · 2012-10-20 15:13:43Z

2

You are rturning ID as rowNumber, so it simply returning ID column value. Why do you expect it to be different?

I think you may want to define and @curRow variable to get the row number as and use sub query as below:

 SELECT * from 
    (SELECT  ID, 
         NAME, 
         @curRow := @curRow + 1 AS rowNumber
      FROM    Teams t
      JOIN    (SELECT @curRow := 0) curr
      ORDER by t.ID asc) as ordered_team
 WHERE ordered_team.id = 4;

edited Oct 20, 2012 at 15:13

answered Oct 20, 2012 at 15:03

Yogendra Singh

34.5k7 gold badges66 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JNK Over a year ago

I know, that this is not the real row number. This only works if I do not delete a team as auto increment takes care of the ids.But I will need to be able to delete teams in my application.

Yogendra Singh Over a year ago

Your auto increment increments the ID column value while inserting the records. It doesn't move backward i.e. update the ID value when some record is deleted. Try the modified query. It should help you achieve what you are looking for.

Yogendra Singh Over a year ago

@JNK I updated the query to help you achieve single row select. Please try. This should help you.

Anton Bessonov · Accepted Answer · 2012-10-20 15:17:48Z

2

It's not a good way, but with plain sql:

SELECT
    t.id,
    t.name,
    (SELECT COUNT(*)+1 FROM teams WHERE id < t.id) as row_number
FROM teams t
WHERE t.id = 4

answered Oct 20, 2012 at 15:17

Anton Bessonov

9,8933 gold badges41 silver badges39 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:08:56Z

0

Why do you bother row indexes inside the persistance layer?

If your really need to rely on the "index" of the tupples stored, you could introduce a variable and increment it in the query/ program code for each row.

EDIT: Just found that one:: With MySQL, how can I generate a column containing the record index in a table?

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Oct 20, 2012 at 15:05

Sven

1321 silver badge10 bronze badges

6 Comments

JNK Over a year ago

But this doesn't work when I only select one row as it will always return 1.

Sven Over a year ago

I think I got the question wrong... You want the first row returned to be numbered as #1 and the second one as #2? Even if you delete some rows in between? Why do you need those index IDs?

JNK Over a year ago

I want to select one row where id is x and get the rows "index" (imagine the table is an array) in the table/array.

Sven Over a year ago

In that case I would stick to the answer above and don't care about the real index inside the persistance layer. MySql/MS Sql etc. take care of that. Your auto_increment column in the only index that matters for you - although you could introduce another primary key for your table. Your Array should consist of <primary key> => <values>

Sven Over a year ago

Well... I would implement this like: One table for each possible station, on for the teams and one for the visited stations per team (link table). You could then query against the link table using a DISTINCT modifier to retrieve the unique station visited per team. If this one equals the number of possible stations your team has finished. There should also be an identifier for the event taking place or something like that.

|

Collectives™ on Stack Overflow

SELECT index of single row in table

3 Answers 3

3 Comments

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related