Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003

    Unanswered: get entry with the latest date

    I have a table that look like follows

    TABLE: History
    history_item_id (autonumber)
    id (primary key)
    street name
    street num
    old street name
    old street num

    The table is basically used to keep a log of all changes made to another table i use. I wanted to run a query that would generate the latest record for each id... if my table looked like the one below for eg"

    34|1|test1|john street|23|john street|45|2003-08-03
    35|1|test1|john street|56|john street|23|2003-08-05
    36|1|test1|john street|523|john street|56|2003-08-05
    37|2|test2|water street|1|water street|2|2003-08-05
    38|2|test2|water street|11|water street|1|2003-08-05
    39|2|test2|water street|12|water street|11|2003-08-05

    the query should return:
    36|1|test1|john street|523|john street|56|2003-08-05
    39|2|test2|water street|12|water street|11|2003-08-05

    If someone could help me out i would be very grateful
    Thanks in advance !!!

  2. #2
    Join Date
    Jun 2003
    the example is hard to understand. the data is mostly the same.

    I am not sure what you are asking. Can you make it more clear?

  3. #3
    Join Date
    Jul 2003

    Just to clarify... are you trying to select the most recent entry by each user? If this is the case, you could try the following...

    CREATE TEMPORARY TABLE $myTable (SELECT * FROM history ORDER BY userId, history_item_id DESC);
    SELECT * FROM $myTable GROUP BY id;

    Not sure if you can do this in one select statement (without subselects), as mysql can't order within groups... only the returned group values themselves... which would mean you keep getting the first sequenced record for each grouped user. Someone may believe this is not the case... anyone wish to clarify?

    I have used the history_item_id for sorting, which assumes it is unique and sequential, as the lastUpdated values you have seem to be of type 'date'. I would probably change your lastUpdated column type to 'datetime' and insert a time component with each date... then change the history_item_id value in the "CREATE TEMP..." statement above to lastUpdated... so you are truely ordering by the date value.

    Last edited by asherh; 09-10-03 at 02:29.

  4. #4
    Join Date
    Apr 2002
    Toronto, Canada

Posting Permissions

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