1

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?

JNK
  • 1,753
  • 8
  • 25
  • 37

3 Answers3

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;
Yogendra Singh
  • 33,927
  • 6
  • 63
  • 73
  • 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. – JNK Oct 20 '12 at 15:06
  • 1
    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 Oct 20 '12 at 15:09
  • 1
    @JNK I updated the query to help you achieve single row select. Please try. This should help you. – Yogendra Singh Oct 20 '12 at 15:16
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
Anton Bessonov
  • 9,208
  • 3
  • 35
  • 38
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?

Community
  • 1
  • 1
Sven
  • 132
  • 1
  • 10
  • But this doesn't work when I only select one row as it will always return 1. – JNK Oct 20 '12 at 15:10
  • 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? – Sven Oct 20 '12 at 15:16
  • 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. – JNK Oct 20 '12 at 15:19
  • 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 => – Sven Oct 20 '12 at 15:21
  • I need this row number because there will be a number of stations and every team needs to finish every station. Imagine there are 16 stations: I simply do rowNumber % 16 to determine where the team has to start. The thing about the array was just to make it more clear :) – JNK Oct 20 '12 at 15:27
  • 1
    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. – Sven Oct 20 '12 at 15:33