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 ( EMPNAME VARCHAR(60), HIRED DATE, ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (ID), KEY OrderOfHire(HIRED,ID) ) ENGINE=MEMORY; Load the Results of your Query INSERT INTO tmpEmpFromState (HIRED,EMPNAME) SELECT HireDate,CONCAT(LastName,', 'FirstName) FROM... WHERE EmpState='ME' 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" <[EMAIL PROTECTED]> To: "MySQL List" <mysql@lists.mysql.com> 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?) -James -- 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]
