Re: finding a particular record within a MySQL result set

Maybe I'm way off here, but when you're looking for his numbered place in the record set, can't you just count the people before him? ID NAME STATE DATE 1 Joe Maine 2006-02-12 2 Jim Maine 2006-01-12 3 James Maine 2006-03-12 4 John Maine 2005-05-12 5 Jordan Maine 2006-02-11 Pseudo-sql below that is untested and probably has a typo in it: SELECT count(*) FROM employees WHERE STATE='Maine' AND DATE < (SELECT DATE FROM employees WHERE ID=1); You count the number of people that have a starting date before his and the same state. Wouldn't he be the next record after that? Depending on if you want the count from 0 or 1, change the < to <=. "James Tu" <jtu@esidesign.com> wrote in message news:A3BF5223-6914-4416-BEC5-0E83DCFABD48@esidesign.com... > I've cross posted this to the MySQL list... > > Here's my original post. > >> Is there some quick way to do the following in MySQL? (I know I can use >> PHP to search through the result set, but I wanted to see if there's a >> quick way using some sort of query) >> >> Let's say I know that Joe is from Maine. >> I want to do a query of all employees from Maine, ordered by hiring >> date, and figure out where Joe falls in that list. (i.e. which record >> number is he?) >> >> -James >> > > > Here's my new plan of attack... > > Right now I'm trying to use PHP to do a binary search on the result set > so I don't have to traverse the entire result set. > > I'm using PHP's mysql_data_seek() to move the pointer within the result > set and looking at the data to do the necessary comparisons. > > What do people think of this approach? > > -James