Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2004
    Posts
    5

    Unanswered: SELECT last modified data

    Hi,

    I have data that sometimes will be modify. when modify the new data will be insert and the old data still remain inside the database. The same item have their own unique id. So I want to query the data that last modified. How?



    Thanks

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    If the only way of knowing what data is the most recent is by it's own ID, you might want to look up group by and max in BOL.

  3. #3
    Join Date
    Sep 2004
    Posts
    5
    They have 1 unique ID and it fixed, so whenever the item been modified, the old data still remain unchanged then a new data will be insert into database with the same unique ID, let say I have items that modified 5 times, so inside my database I have 6 records for the same item, so how can I select the last modified data.


    hope my explaination more better....


    Thanks
    Jai

  4. #4
    Join Date
    Sep 2004
    Posts
    15
    Hi,
    You can add another field to your table of type DateTime. When a new row is inserted, give this column the value of GetDate() function. Write an update trigger to update this new field. Then you can write a query to select the rows those have had change since a particular time.

    Leila

  5. #5
    Join Date
    Sep 2004
    Posts
    5
    yes, I already have the field for modified date, I don't update the current row, I just insert new one without interrupt any other rows because my program must not do that.so now I just want to query last modifed row for that particular item.

    Thanks
    Jai

  6. #6
    Join Date
    Feb 2004
    Posts
    492
    select max(modified date) from ... where id = ... ?

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If id and date_modified is your primary key, then to list only the last row for each id value I'd use:
    Code:
    SELECT a.*
       FROM myTable AS a
       WHERE  a.date_modified = (SELECT Max(b.date_modified)
          FROM myTable AS b
          WHERE  b.id = a.id)
    -PatP

  8. #8
    Join Date
    Sep 2004
    Posts
    5
    Thank guys,

    I solved the problem now, once again I say thanks.


    Jai

  9. #9
    Join Date
    Sep 2004
    Posts
    22
    Quote Originally Posted by jai_ts
    Thank guys,

    I solved the problem now, once again I say thanks.


    Jai
    Personally i would of created a composite primary key.

    ie
    original row
    1, 1 , blah, blaah , lastmodifieddate

    when modified
    1, 2, blah , beuh, lastmodifieddate

    another original row
    2, 1, feh, eh feh, lastmodifieddate

Posting Permissions

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