Re: finding a particular record within a MySQL result set

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx> wrote in message 
news:A3BF5223-6914-4416-BEC5-0E83DCFABD48@xxxxxxxxxxxxxxxx
> 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 

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux