# Database Discussions > MySQL >  Select maximum with limits

## mpselby

Hi, I have not posted before so apologies if this is not set out the way expected.

I have a primary table tPerson containing personal details.  tPerson has relationships with may other tables.  tPerson.IdPerson is the primary key.  Any time a record in tPerson is updated, a trigger puts the old record into tPersonAudit like this: 

INSERT INTO tPersonAudit SELECT * FROM tPerson WHERE IdPerson = OLD.IdPerson;.

Trigger also makes sure IdPerson cannot be changed so we are sure that every record in tPersonAudit is related to correct tPerson record.  Trigger also does: 

SET NEW.eTime = NOW();

which means tPerson and tPersonAudit combined have a full history of what the records in tPerson have been.  I want to query them to get the tPerson record as it looked at some time in the past which means finding the maximum eTime, under a limit for a specific IdPerson.  I have solved it with the following procedure but it would be much neater if I could do it with a view as I have many such pairs tables that I want to query together with joins.  Any insight would be much appreciated!

Proc is as follows:

-- get the tPerson table as it was at timeStamp
CREATE PROCEDURE `GetPersonData`(IN timeStamp DATETIME)
BEGIN

  SELECT
    tempPerson.*
  FROM
    (
      SELECT 
        TEMP_PERSON_TABLE.*
      FROM
        (
          SELECT 
            tPerson.*
          FROM
            tPerson
          WHERE
            eTime <= timeStamp 
          UNION
          SELECT
            tPersonAudit.*
          FROM
            tPersonAudit
          WHERE 
            eTime <= timeStamp
        ) AS TEMP_PERSON_TABLE
	INNER JOIN
	(
          SELECT
            IdPerson, MAX(eTime) AS eTimeMax
          FROM
            (
              SELECT
                tPerson.*
              FROM
                tPerson
              WHERE
                eTime <= timeStamp
              UNION
              SELECT
                tPersonAudit.*
              FROM
                tPersonAudit
              WHERE
                eTime <= timeStamp
            ) AS UNION_PERSON_TABLE
	  GROUP BY IdPerson
        ) AS MAX_PERSON_TABLE
        ON 
          MAX_PERSON_TABLE.eTimeMax = TEMP_PERSON_TABLE.eTime
          AND
          MAX_PERSON_TABLE.IdPerson = TEMP_PERSON_TABLE.IdPerson
    ) AS tempPerson

END

----------


## mpselby

Didn't realise site would strip the leading whitespace. Attached has proc in a slightly more readable format.

----------


## eval(BadCode)

I wouldn't use a trigger for something that a ON UPDATE CURRENT_TIMESTAMP field could do.

You can also use the trigger for AFTER UPDATE instead of an insert trigger. (its cleaner)

I also have no idea why you're inner joining these tables togeather... Is your problem not as easy as this:




```
CREATE PROCEDURE `GetPersonData`(IN timeStamp DATETIME, person_ID INT)
BEGIN

SELECT * FROM tPerson
UNION ALL
SELECT * FROM tPersonAudit

WHERE IdPerson = person_ID
AND     etime <= timeStamp

ORDER BY etime ASC

END
```

P.S. You can keep the whitespace if you put the code between [C O D E] [/ C O D E]  tags (no spaces).

----------

