Then you just have to come up with some other criteria for determining who should be counted as "before" or "after" Joe, which might well be the same as the order by clause in whatever you're doing right now while examining the result set.
I think your approach of examining the result set will work well in testing. In practice, with load, it could quickly become a real problem. Databases are very good at providing answers about the data they contain, while languages like PHP are very good at emitting HTML ... use each to its fullest potential. Looping through thousands of results provided by the database to find one record is not efficient in terms of either the database or PHP. Dan On 4/4/07, James Tu <[EMAIL PROTECTED]> wrote:
That is a nice idea, I'll have to keep it in my bag of tricks. However, I don't know if it will work b/c there are probably others that are hired on the same date... On Apr 4, 2007, at 1:51 PM, Dan Buettner wrote: > James, one option would be to run a query to find the number of > people in > the list ahead of him, rather than determining position within the > result > set. > > As in: > > SELECT COUNT(*) FROM some_table > WHERE state = "Maine" > AND hire_date < (SELECT hire_date FROM some_table > WHERE last_name = "Smith" > AND first_name = "Joe" > AND state = "Maine") > > Dan > > >> On Mar 22, 2007, at 11:21 AM, James Tu wrote: >> > >> > > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
