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
On 4/4/07, James Tu <jtu@stripped> 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/mysql?unsub=1