This may sound a little cheesy, but hear me out.
Create a temp table in memory holding the result of the your employee query like this:
CREATE TEMPORARY TABLE tmpEmpFromState
ID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (ID),
Load the Results of your Query
INSERT INTO tmpEmpFromState (HIRED,EMPNAME)
SELECT HireDate,CONCAT(LastName,', 'FirstName)
ORDER By HireDate;
The table tmpEmpFromState is now loaded in order by HireDate.
If on any given date, multiple employees were hired,
then the employees that were hired on the same date
will be ordered chronologically by the order in which
the employee was enter in the database.
If the table with the HireDate inquery select query has a timestamp,
then order your select by hirdate,timestamp to guarantee this ordering scheme.
Now get ID from tmpEmpFromState for Employee Joe Cool
SELECT ID FROM tmpEmpFromState WHERE EMPNAME='Cool, Joe';
Give it a try.
----- Original Message -----
From: "James Tu" <jtu@stripped>
To: "MySQL List" <mysql@stripped>
Sent: Thursday, March 22, 2007 11:21:34 AM (GMT-0500) Auto-Detected
Subject: Finding a record in a result set
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?)
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=1