Getting record postion in mysql

[anoopabose]

hi all,
my problem is, i want to get a particular record postion from resultset

eg. a select query
select * from table

i got record like this

Id Name
111 saju
112 raj
156 sreeju
187 neela

the id 156 is in 3rd postion .I want the Postion Number Not Ihe ID. Any mysql function is available for getting the record postion from this output.Anybody knows please help me

regards
anoop

[devinemke]

you just want ID#156? then just do:

SELECT * FROM table WHERE ID = 156

[Sxooter]

OK, first and foremost, without an order by, theoretically, running that query two times could result in an output like:

Id Name
111 saju
112 raj
156 sreeju
187 neela

the first time and

Id Name
187 neela
111 saju
156 sreeju
112 raj

the second time. I.e. with no order by there is NO ORDER in a SQL set, except that incidentally created by the operation of your database. In fact, with different table handlers in MySQL you could get different ordering caused by updates in one table handler than you'd get in another. And that is completely normal.

So, the first step here is to add an order by somewhere:

select * from table order by id.

Now, how to get the "row" number.

You can either have the database do it, or do it in PHP. In PHP, you can do it a couple of ways. The easiest is to just add it as you process each row. Or do you want the db to do it? And do these numbers need to stay the same even if you insert a new line in between the currently existing rows.

Your usage determines your methods