List:General Discussion« Previous MessageNext Message »
From:James Tu Date:March 26 2007 6:08pm
Subject:Re: Finding a record in a result set
View as plain text  
Thanks Maciek:

The table that I'm doing this query on will be huge.  It's  
essentially the users table for an online activity with, we hope,  
lots of users.  :)
The thing is that if I do a query for the entire result set and use  
PHP to figure out the position of the user and then do a query on a  
page of results that will contain the user, I'm still going to take a  
hit right?

Or are you concerned about performance b/c MySQL and subqueries are  
really slow?

-James

On Mar 23, 2007, at 8:05 PM, Maciej Dobrzanski wrote:

> "James Tu" <jtu@stripped> wrote in message =
> news:FFE2B162-EDC4-43F3-BF89-E798FBA1F97E@ style="color:#666">stripped...
>
>> I want to do a query of all employees from Maine, ordered by  
>> hiring =20
>> date, and figure out where Joe falls in that list. (i.e. which  
>> record  =
>
>> number is he?)
>
> I think this can only be accomplished with a temporary table.
>
> SET @n:=3D0;
> SELECT t.name, t.n FROM (SELECT @n:=3D@n+1 AS n, name FROM t WHERE  
> state =
> =3D 'Maine' ORDER BY hire DESC) t WHERE t.name =3D 'Foo';
>
> This query though may not be suitable for most situations as its =
> performance depends heavly on the size of the derived table.=20
>
> Maciek
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql? 
> unsub=jtu@stripped
>
>


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