Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: best way to compare last row with new insert

    Hi !
    for MS SQL 2000

    I need an UsersHistory Table where i cannot INSERT 2 rows one after the other with the same user.name

    I can get

    Bob
    David
    Bob


    but not

    Bob
    Bob
    David


    something like an INDEX on rows
    or
    INSERT INTO UsersHistory (name) VALUES ('bob') IF MAX(name) <> 'bob' ?

    what is the best way to do it ?

    thank you
    Last edited by anselme; 01-21-07 at 03:00.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    You need some other column to order by, say a datetime. it could store when the user last logged in/logged out/whatever. Why? because the physical order of rows in a table has no meaning. Since you are storing a history, the thing you want to order by is a time, so you need a datetime column.

    Then you could do something like this:

    Code:
    -- assumes you already have a username in a @name variable
    declare @time datetime
    select @time = max(LoggedInAt) from UsersHistory 
    
    if not exists(select * from UsersHistory where LoggedInAt=@time and UserName=@name)
    begin
       insert into UsersHistory (UserName, LoggedInAt) values (@name, getdate())
    end
    Last edited by jezemine; 01-21-07 at 03:38.

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    I have allready that datetime column = getdate()

    but i dont understand the meaning of your query , if the last user row is Bob the next row cannot be Bob, it doesnt depend on the DateTime it can be 10 minutes ... one year
    I must have an history of LastUsers for certains events in the database, the LastUser is the Last one as long as nobody do something and replace the last one (I know my english is terrific ! :-))

    the last inserted row is also the MAX(id)

    thanks a lot Jezemine
    Last edited by anselme; 01-21-07 at 22:38.

  4. #4
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    Do something like:

    INSERT INTO UsersHistory (name) VALUES ('bob') IF (SELECT fname from UsersHistory where id = max(id)) <> 'bob'

    Note: not actual code

Posting Permissions

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