Finding the row number of a select

[frigidman]

Mysql and PHP.

Say I have a complex select statement that grabs a good 100 or so records.

Now, one of those records I know the ID of, but want to pull what row number it is at.

Like for instance the ID of 635252 is row 26 out of 103 records that would be returned from a complex WHERE claus.

Am I smoking crack?

Is finding this number easy to get through mysql, or do I just do the full query, run a while loop with a counter, and when I come to the row with the ID i am trying to find, then I just break out and use that number? Thats the rude crude blunt way, but I would rather know of a built in mysql method for pulling that id out without having to go retrieve rows of data to find out in php.

?

Thanks for input.

[tomhath]

I suppose you could use COUNT(*) and modify the query to only select the rows before the one of interest. But that would get too tricky for me, it seems like asking for bugs. In this case I'd stick with the KISS principle.

[Anon]

You seem to be using row number and id interchangeably. Row number looks like an aggregated value.

Row number isn't an id - those who use LIMIT, OFFSET to find a row are asking for trouble.

[frigidman]

What does any of that have to do with my question?

Regarding my actual question, say my table is of:

TABLE USERS(
USERID int auto_increment primary key,
FULLNAME)

So, I KNOW what user ID I will need to use, but if I grab 10 users based on a criteria, I want to know where the user ID of 523 is located in the 10, is it spot 3, or 7.

[frigidman]

Yeah, I have gone for the KISS method. Doing the while loop on just the id's using the exact same WHERE claus, seems to work fine.