Results 1 to 3 of 3

Thread: Select maximum with limits

  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Select maximum with limits

    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

  2. #2
    Join Date
    Mar 2011
    Posts
    2

    neatly formatted!

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

  3. #3
    Join Date
    Apr 2011
    Location
    /ramdisk/
    Posts
    6
    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:


    Code:
    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).

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •