List:General Discussion« Previous MessageNext Message »
From:Rolando Edwards Date:March 22 2007 4:33pm
Subject:Re: Finding a record in a result set
View as plain text  
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" <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?)

-James


-- 
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