Post reply

get last iserted records

  • October 6, 2008 at 2:38 am

    #77101

    hello friends

    whats the best way to get las X records inserted in a Table ?

  • October 6, 2008 at 3:08 am

    #881001

    if you have datetime column in your table, then select max(datetime column) from will give you the expected result.

    karthik

  • October 6, 2008 at 3:19 am

    #881004

    karthikeyan (10/6/2008)

    if you have datetime column in your table, then select max(datetime column) from will give you the expected result.

    no,theres no DateTime

  • October 6, 2008 at 3:24 am

    #881005

    I am sure you will get best replies from here if you post the table structure with sample data.

    karthik

  • October 6, 2008 at 3:27 am

    #881007

    What about an incrementing id column?

    "Keep Trying"

  • October 6, 2008 at 3:36 am

    #881009

    Chirag (10/6/2008)

    What about an incrementing id column?

    i have incrementing entity column but you know ?

    im going to write a procedure that call from my application each 3,4 and even per second.and in this period some records are added to Table.i need to process in last 50 records of table each time procedure call.

    with this amount of process,i think its not ok to have mathematical process to count for last X records again!

    if you suggest a way,ill appriciate you 🙂

  • October 6, 2008 at 4:02 am

    #881014

    hum..sorry if i coudnt explain clearly,let me know this :

    whats the best way (from performance point of view) to select last X records from a Table

  • October 6, 2008 at 6:22 am

    #881050

    consider this table :

    CREATE TABLE [dbo].[Customer] (

    [Id] [int] IDENTITY (1, 1) NOT NULL ,

    [Code] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [Family] [nvarchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

    ) ON [PRIMARY]

    how can i get last Id from Customer Table in my procedure?

    (i dont insert into Customer table in my procedure,i just need last id in my procedure)

    thanks

  • October 6, 2008 at 6:35 am

    #881055

    DECLARE @MaxID INT

    SELECT @MaxID = Max(ID) FROM Customer

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 6, 2008 at 6:47 am

    #881065

    GilaMonster (10/6/2008)

    DECLARE @MaxID INT

    SELECT @MaxID = Max(ID) FROM Customer

    which one is better :

    i save @@Identity in a field when record insert in Table and i read saved id in my procedure instead to use select max(ID)..

    because of performance

  • October 6, 2008 at 7:15 am

    #881083

    @@identity will get you the identity value that you just inserted. MAX(ID) will get you the last value inserted. If multiple users cn be inserting at the same time, the two values may not be the same.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 6, 2008 at 7:32 am

    #881092

    GilaMonster (10/6/2008)

    @@identity will get you the identity value that you just inserted. MAX(ID) will get you the last value inserted. If multiple users cn be inserting at the same time, the two values may not be the same.

    i know,i think i didnt ask clearly !let ask again :

    i have a customer table and two procedure :

    1-procedureInsert

    2-procedureAnalyze

    i mean when user Insert record in customer table we can save last id (by using @@Identity or Scope_Identity ) in a field(when use procedureInsert )

    and when we wanna analyze inserted records,we use procedureAnalyze and instead of use select max(id) in procedureAnalyze we van use the field that stored by procedureInsert before.

    now,isnt this way better ?

  • October 7, 2008 at 4:32 am

    #881531

    Hi,

    AS the values are changing every second the in procedure we should use

    WITH RECOMPILE, So wheen ever it is called it recompiles the query and get the last 50 records no doubt what is the last one.

    Regards,

    Syed Sanaullah Khadri.

  • October 7, 2008 at 5:48 am

    #881568

    dr_csharp (10/6/2008)

    GilaMonster (10/6/2008)

    DECLARE @MaxID INT

    SELECT @MaxID = Max(ID) FROM Customer

    which one is better :

    i save @@Identity in a field when record insert in Table and i read saved id in my procedure instead to use select max(ID)..

    because of performance

    My recommendation is to NEVER EVER use @@Identity because if you someday put an audit trigger on the table, @@Identity could return the wrong values. Use SCOPE_IDENTITY() instead.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 7, 2008 at 6:41 am

    #881598

    Jeff Moden (10/7/2008)

    karthikeyan (10/6/2008)

    if you have datetime column in your table, then select max(datetime column) from will give you the expected result.

    No... if someone happens to do an Insert at the same time, you may get the wrong rows. Don't ever do it this way. And don't even think of using a transaction with and "Insert/Select" to do it... guaranteed recipe for more deadlocks than you can shake a stick at.

    EDIT... let me make a correction now that I've "read down" in the thread a bit. You can do it that way if you want the last 50 rows... just keep in mind that what you have as the "last 50 rows" could change in a heartbeat.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 15 posts - 1 through 15 (of 15 total)

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