List:General Discussion« Previous MessageNext Message »
From:Francesco Riosa Date:March 23 2007 8:34pm
Subject:Re: Finding a record in a result set
View as plain text  
Maybe this one could do (untested) ?

SELECT COUNT(*),
( SELECT count(*) FROM employees AS t2 WHERE t2.name = 'Joe' AND 
t2.state = 'ME' AND t2.hiredate <= <datevalue>) AS joesexist
FROM employees
WHERE name <= 'Joe' AND state = 'ME' AND hiredate <= <datevalue>;


Jerry Schwartz ha scritto:
> I don't think that will work. If there are 1,000 records that qualify but
> none for Joe, then it will return 1,001. If Joe is in record 1 of the
> retrieved record set, and there are 999 other people who match the WHERE
> clause, then it will retrieve 1,000.
>
> Am I missing something?
>
> Regards,
>
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
>
>   
>> -----Original Message-----
>> From: Peter Brawley [mailto:peter.brawley@stripped]
>> Sent: Thursday, March 22, 2007 12:33 PM
>> To: James Tu
>> Cc: MySQL List
>> Subject: Re: Finding a record in a result set
>>
>>  >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?)
>>
>> If 'Joe' is a unique name LOL...
>>
>> SELECT 1 + COUNT(*)
>> FROM employees
>> WHERE name <> 'Joe' AND state = 'MA' AND hiredate < <datevalue>;
>>
>> PB
>>
>> 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
>>>
>>>
>>>
>>> --No virus found in this incoming message.
>>> Checked by AVG Free Edition.
>>> Version: 7.5.446 / Virus Database: 268.18.17/730 - Release Date:
>>> 3/22/2007 7:44 AM
>>>
>>>
>>>       
>> --
>> 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