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:[EMAIL PROTECTED]
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/[EMAIL PROTECTED]
--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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
