List:General Discussion« Previous MessageNext Message »
From:Dan Buettner Date:April 4 2007 7:18pm
Subject:Re: Finding a record in a result set
View as plain text  
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 <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
>
>

Thread
Finding a record in a result setJames Tu22 Mar
  • Re: Finding a record in a result setPeter Brawley22 Mar
    • Re: Finding a record in a result setRolando Edwards22 Mar
    • RE: Finding a record in a result setJerry Schwartz22 Mar
      • Re: Finding a record in a result setFrancesco Riosa23 Mar
      • SPAM ?Jacques Brignon24 Mar
        • Re: SPAM ?LP24 Mar
        • Re: SPAM ?Martijn Tonies24 Mar
        • Re: SPAM ?Stephen Cook25 Mar
          • RE : SPAM ?Jacques Brignon25 Mar
            • RE : RE : SPAM ? SOLVEDJacques Brignon25 Mar
        • Re: SPAM ?Banyan He25 Mar
          • RE : SPAM ?Jacques Brignon25 Mar
            • Re: SPAM ?Banyan He25 Mar
  • Re: Finding a record in a result setRolando Edwards22 Mar
  • Re: Finding a record in a result setMaciej Dobrzanski24 Mar
    • Re: Finding a record in a result setJames Tu26 Mar
  • Re: Finding a record in a result setJames Tu26 Mar
  • Re: Finding a record in a result setJames Tu4 Apr
    • RE: Finding a record in a result setJerry Schwartz4 Apr
      • Re: Finding a record in a result setDan Buettner4 Apr
        • Re: Finding a record in a result setJames Tu4 Apr
          • Re: Finding a record in a result setDan Buettner4 Apr
      • Re: Finding a record in a result setJames Tu4 Apr