Select last inserted record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select last inserted record

Author

Topic

oepirobo
Starting Member

36 Posts

Posted - 2003-09-17 : 17:27:53

How do I select the last or latest inserted record from a table in SQL Server?

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-09-17 : 17:50:07

I ususally put a cre_date DATETIME(8) field in all of my tables, and I use that for sorting:


SELECT TOP 1 * FROM myTable ORDER BY cre_date DESC

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-18 : 09:42:20

Mike,

I was testing to see which would have a better execution plan...select top 1 or select where in Max(dt)..

I expected some dups in my sql..but not the amount I got,,granted I looped and did mass inserts...generating about 200k rows...

But I got about 2k rows with the exact same timestamp..down to the microseconds...man that's fast..

SO I gues the question is, that since there is a potential for dups (not on that scale grant you), but how do you get the "last" record entered. There are no gaurentees....

I think his is an Oracle background question

Dare I say it (Nooooooooooooooooooooo)

Identity?

Here's the mess I played with:



USE Northwind
GO

SELECT TOP 1 * INTO OrderDetails_2 FROM [Order Details]
GO

TRUNCATE TABLE OrderDetails_2
GO

ALTER TABLE OrderDetails_2 
	ADD ADD_DT datetime NOT NULL 
	CONSTRAINT cnstrt_OrderDetails2_1 
	DEFAULT getdate() WITH VALUES
GO


CREATE INDEX IX1 ON OrderDetails_2 (ADD_DT)
GO


DECLARE @x int
SELECT @x = 0
WHILE @x < 100
	BEGIN
		INSERT INTO OrderDetails_2(OrderID, ProductID, UnitPrice, Quantity, Discount) 
		SELECT * FROM [Order Details]
		SELECT @x = @x + 1
	END

SELECT COUNT(*) FROM OrderDetails_2
GO

SELECT TOP 1 * FROM OrderDetails_2 ORDER BY ADD_DT DESC
GO

SELECT * FROM OrderDetails_2 o WHERE ADD_DT IN  (SELECT MAX(ADD_DT) FROM OrderDetails_2)
GO


DROP TABLE OrderDetails_2
GO

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-09-18 : 09:57:27

Brett, look at the difference in the execution speeds by making a couple of minor tweaks:


SELECT TOP 1 * FROM OrderDetails_2 ORDER BY ADD_DT DESC
GO

SELECT TOP 1 * FROM OrderDetails_2 o WHERE ADD_DT = (SELECT MAX(ADD_DT) FROM OrderDetails_2)
GO

Owais

Make it idiot proof and someone will make a better idiot

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-18 : 11:01:57

Absolutley...you'rs doesn't even register...getting all zeroes...

My bigger problem is still with which one is last....

Damn...works calling...

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources