Post reply

Retrieve Latest Record

  • July 31, 2008 at 5:30 am

    #191873

    Hi all,

    I have a Table with Id (Uniqueidentifier), Name, RoleId (Uniqueidentifier), UserType Columns

    but there is no Column of Registration Date or something to know when the user joined

    here there may be chance of having same RoleId for more than one Person.

    I need a query to retrieve the record of the person who made first registration, with same RoleId...

    now the site is in live and it's very long process to add a new column for Registration date or some other thing

    thanks in advance

    Prakash.C

  • July 31, 2008 at 7:42 am

    #851251

    Prakash,

    there's no way to find the last record with a query in your design. That's why I always advise to add a createdate and changedate column to all tables.

    [font="Verdana"]Markus Bohse[/font]

  • July 31, 2008 at 8:21 am

    #851283

    Hi Markus Bohse,

    Thanks for u reply, It's good to have created or registration date, but in my case i have left that, now i cannot make any change in Table layout.

    is there any other alternative way to find the latest record....?

    thanks

    Prakash.C

  • July 31, 2008 at 8:58 am

    #851320

    The only thing I can think of is using a logreader tool to see in the transaction log (or backups) when a certain record has been inserted/updated.

    But if this happened some time ago you probably wouldn't find it either.

    [font="Verdana"]Markus Bohse[/font]

  • July 31, 2008 at 9:06 am

    #851327

    A logreader would be the only thing. There's no other way to do this without adding a column.

    You could add a column with a default, which might work for you. Or create a new table, add the default, then create a view on top of it with the name of the old table.

  • July 31, 2008 at 9:48 pm

    #851636

    going forward, you can write an INSERT trigger that copies the ID from your table into another table that has a createdate field. this won't help you find the last record entered into the original table in the past though...

  • July 31, 2008 at 10:26 pm

    #851644

    i think this would help u to retrieve the last record

    select * from tablename where Id in ( select top 1 Id from tablename order by Id desc)

  • August 1, 2008 at 1:52 am

    #851682

    raj (7/31/2008)

    i think this would help u to retrieve the last record

    select * from tablename where Id in ( select top 1 Id from tablename order by Id desc)

    Raj ,

    if you read the original post you will see that the ID field is a Guid, not an integer. Because guids are created randomly you're query won't retrieve the last ID issued.

    [font="Verdana"]Markus Bohse[/font]

  • August 6, 2008 at 8:46 am

    #853873

    Hi All,

    Thanks for all u'r Replies.

    Ok as per u people suggestions i try to add another column to store datetime

    thanks for all

    Prakash.C

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply