1

I want to get the row_number of the original record in the database. Is there any way to retrieve the original row number of a record in mysql? Suppose my statement is 

select course_id from course where subject='finance';

there can be multiple records for this query. Suppose they are from row numbers 4, 5, 7, 9 and 10. How can i retrieve these row_numbers from my query?

codingNewbie
  • 95
  • 2
  • 15
  • There are no actual "row numbers" in most SQL databases. If you need to keep track of the order that rows are added, you need to do that yourself by assigning a sequential ID or a date column that's set when the insert is done. – Ken White Dec 22 '11 at 17:17
  • $num = $mysql_num_rows($res); for ($i=0;$i<=$num;$i++) if (($i==4) or ($i==5) or ($i==7) or ($i==9) or ($i==10)) $arr[] .= $row['course_id']; I supossed you fetch your data before! – devasia2112 Dec 22 '11 at 17:21
  • Did my answer solve your question @codingNewbie or do you need more help? – Derk Arts Dec 23 '11 at 12:48
  • i just used a simple update statement and i realized that i didn't need to retrieve the row number. I didn't want to create rankings. thanks a lot for all your help :) – codingNewbie Dec 23 '11 at 14:00

1 Answers1

1

Check: With MySQL, how can I generate a column containing the record index in a table?

SELECT  c.course_id, 
        @curRow := @curRow + 1 AS row_number
FROM    course c
JOIN    (SELECT @curRow := 0) r;

Do note however, this is artificial as the only real 'row number' would be your own primary ID, and every artificial method will change the row number when you change the ORDER clause

Community
  • 1
  • 1
Derk Arts
  • 3,432
  • 2
  • 18
  • 36