Post reply

How to select the last record added to a table

  • January 7, 2019 at 7:04 am

    #409414

    I am in the process of creating a table of widgets (obviously everything about this example is fictitious including the select *). There are occasions that I need to be able to select the last/most recent widget that was entered into the widget table.

    My assumption is that in order to do this I have to do something like

    Select * from Widgets

    Where some column = Max(some column)

    My question is, what type is that ‘some column’?

    Is it an identity column, a datetime column, a rowversion column or something else?

    In addition, what’s the proper syntax for selected the last widget entered into the table?

    Thanks

  • January 7, 2019 at 7:12 am

    #2018157

    eichnerm - Monday, January 7, 2019 7:04 AM

    I am in the process of creating a table of widgets (obviously everything about this example is fictitious including the select *). There are occasions that I need to be able to select the last/most recent widget that was entered into the widget table.My assumption is that in order to do this I have to do something like Select * from WidgetsWhere some column = Max(some column)My question is, what type is that ‘some column’? Is it an identity column, a datetime column, a rowversion column or something else?In addition, what’s the proper syntax for selected the last widget entered into the table?Thanks

    Depends on the columns in the table. If there is a DateCreated column, for example, you could do this:

    SELECT TOP 1 *
    FROM WIDGETS
    ORDER BY DateCreated Desc

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • January 7, 2019 at 7:13 am

    #2018158

    or instead of MAX you could use TOP(1) with an ORDER BY:
    SELECT TOP(1) *
    FROM dbo.Widgets
    ORDER BY ColumnThatIncreasesWithInsert DESC

    Ideally the ColumnThatIncreasesWithInsert will be indexed to prevent a full table scan.
    This could be an identity or a datestamp.

  • January 7, 2019 at 7:19 am

    #2018159

    Thanks to both of you. I can add anything to the table as I am in the process of creating it. I had thought about using date time as my post indicated. I am curious why an identity column wouldn’t return the last row entered?

  • January 7, 2019 at 7:31 am

    #2018160

    eichnerm - Monday, January 7, 2019 7:19 AM

    Thanks to both of you. I can add anything to the table as I am in the process of creating it. I had thought about using date time as my post indicated. I am curious why an identity column wouldn’t return the last row entered?

    It should, but it's not as bulletproof as using DateCreated. 
    If someone has added rows after issuing 
    SET IDENTITY_INSERT WIDGETS OFF
    for example, your query would potentially return the wrong data.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • January 7, 2019 at 7:32 am

    #2018162

    eichnerm - Monday, January 7, 2019 7:19 AM

    I am curious why an identity column wouldn’t return the last row entered?

    It will, provided the identity column is never re-seeded, or updated independently (by using SET IDENTITY_INSERT).
    And do you you want to identity of the last row inserted, regardless of which process inserted it, or the last row inserted within the current batch?
    Have a look at SCOPE_IDENTITY if that might be an issue.

    Steve Hall
    Linkedin
    Blog Site

  • January 7, 2019 at 7:40 am

    #2018163

    Thanks again to both of you. I hadn’t considered the issue of re seeding. I’m familiar with SQL Server, but not experienced enough to have seen all the gotcha’s.

    Am I correct that I shouldn’t have to worry about two inserts having the exact same value when using datetime2? I really can’t see that happening in my app, I am just curious.

    Thanks again

  • January 7, 2019 at 7:53 am

    #2018166

    Time range 00:00:00 through 23:59:59.9999999

    I wouldn't lose any sleep over that, personally.

    Steve Hall
    Linkedin
    Blog Site

  • January 7, 2019 at 8:10 am

    #2018170

    BrainDonor - Monday, January 7, 2019 7:53 AM

    Time range 00:00:00 through 23:59:59.9999999

    I wouldn't lose any sleep over that, personally.

    Still, if it's critical that they not be identical, there's absolutely nothing that prevents it.  Better have a "Plan B" on that.

    Also.. if more than one row is in the same insert, the chances of having duplicate times rises to about 100%.

    --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

  • January 7, 2019 at 8:25 am

    #2018176

    Thanks. I’ll keep that in mind. In my application the widgets table is rarely modified and it’s the only table that’s affected.

    Given that I suspect it’s not a problem.

  • January 7, 2019 at 8:31 am

    #2018180

    eichnerm - Monday, January 7, 2019 8:25 AM

    Thanks. I’ll keep that in mind. In my application the widgets table is rarely modified and it’s the only table that’s affected. Given that I suspect it’s not a problem.

    Never assume, plan for that which won't change to change.

  • January 7, 2019 at 8:59 am

    #2018185

    eichnerm - Monday, January 7, 2019 7:19 AM

    Thanks to both of you. I can add anything to the table as I am in the process of creating it. I had thought about using date time as my post indicated. I am curious why an identity column wouldn’t return the last row entered?

    I'd go belt and braces with a unique key column (data type IDENTITY) and a DateCreated column (type DATETIME, default GETDATE()).
    If you're inserting multiple rows in a batch, does it matter which is seen as the last one inserted? If so, you would need to tweak your batch load process

  • January 7, 2019 at 10:59 am

    #2018195

    That’s an interesting thought. I think it might make sense for my particular situation.

Viewing 13 posts - 1 through 12 (of 12 total)

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