How to select the last record added to a table

Question

How to select the last record added to a table

eichnerm

Right there with Babe

Points: 748
More actions
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

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

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

Phil Parkin SSC Guru Points: 246964 More actions · Answer 1

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 2

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.

eichnerm Right there with Babe Points: 748 More actions · Answer 3

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?

Phil Parkin SSC Guru Points: 246964 More actions · Answer 4

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

BrainDonor SSCoach Points: 19254 More actions · Answer 5

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

eichnerm Right there with Babe Points: 748 More actions · Answer 6

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

BrainDonor SSCoach Points: 19254 More actions · Answer 7

Time range

00:00:00 through 23:59:59.9999999

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

Steve Hall
Linkedin
Blog Site

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

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)

eichnerm Right there with Babe Points: 748 More actions · Answer 9

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

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.

mark_p Valued Member Points: 53 More actions · Answer 11

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

eichnerm Right there with Babe Points: 748 More actions · Answer 12

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