LATEST RECORD

Question

Post reply

LATEST RECORD

sahoong

SSCrazy

Points: 2545
More actions
July 27, 2009 at 1:17 am

#208785

The Gurus,
Could someone tell me what code I can use to obtain the latest record that he just been added to a table. I administer a table where newly placed orders are stored. In order to process new orders, I need to identify newly added records over the last few minutes or thereabout.
Many thanks in advance.
Regards,
Sahoong.

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

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

Do you have a date column on the table? or identity columns? you can query the table using ORDER BY clause.

Pradeep Singh

sahoong SSCrazy Points: 2545 More actions · Answer 2

Sorry, no date column but there is a primary key column. Can u give me a suitable t-sql code to use in all possible scenario. Tx.

ps. One Orange Chip Points: 29422 More actions · Answer 3

If the primary key is numeric and keeps on increasing, this code should just do fine.

select top 10 * from YourTable ORDER BY yourPrimayKeyColumnName DESC

Pls share some primary key values that ur table have....

Pradeep Singh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Can you post the table design?

SQL doesn't keep track of the dates or order that data is inserted in. If you want to retrieve the latest row, there has to be a column in the table that either specifies the data and time inserted (datetime) or the order that rows were inserted (identity, newsequentialguid). If you have neither of those, then there is absolutely no way of telling, after the fact, what the latest inserted row was.

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

Brian Burke Mr or Mrs. 500 Points: 590 More actions · Answer 5

presuming you are doing this in the context of an SP and you want to immediately get a handle to the inserted column you can do the following.

Add identity column to the table

When the SP inserts, use the output..inserted command (example below) to output the value for the ID col.

CREATE TABLE [dbo].[tableName](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[Col1] [nvarchar](50) NOT NULL,

[Col1] [nvarchar](50) NOT NULL

CONSTRAINT [PK_tablename] PRIMARY KEY CLUSTERED ([ID] ASC))

Declare @tmpTable table(IDVal bigint)

Declare @idVal bigint

INSERT INTO [dbo].[TableName]

([Col1],[Col2])

output Inserted.ID into @tmpTable

VALUES

(@val1,@val2)

Select @IDVal=[IDval] from @tmpTable

However if you are doing this outside of the scope of an sp then a column with getdate() set as the default might be your best bet, only this is not guaranteed, particularily if you have high throughput

Animal Magic SSChampion Points: 13964 More actions · Answer 6

sahoong (7/27/2009)
The Gurus,
Could someone tell me what code I can use to obtain the latest record that he just been added to a table. I administer a table where newly placed orders are stored. In order to process new orders, I need to identify newly added records over the last few minutes or thereabout.
Many thanks in advance.
Regards,
Sahoong.

All the suggestions above will tell you how to get the latest records, however if you want to know which orders to process why not just have a "processed" flag, default it to 0 and then have your job pick up and process and records with a processed flag of 0?

mwebster-1117730 SSC Enthusiast Points: 125 More actions · Answer 7

The above suggestions are much preferred, but you should also be able to tell from the transaction logs, if you have backups you can access.

Try this software from redgate...Redgate SQL Log Rescue. The software is free.

I haven't actually used this software, but I used something similar at one point.

Animal Magic SSChampion Points: 13964 More actions · Answer 8

mwebster (7/31/2009)
The above suggestions are much preferred, but you should also be able to tell from the transaction logs, if you have backups you can access.
Try this software from redgate...Redgate SQL Log Rescue. The software is free.
I haven't actually used this software, but I used something similar at one point.

Please note that SQL Log Rescue does not support 64-bit versions of SQL Server or SQL Server versions other than SQL Server 2000. Probably why its free now, it never used to be.

mwebster-1117730 SSC Enthusiast Points: 125 More actions · Answer 9

Doh, thanks for letting me know. Since this is in the SQL 2005 section, no help then.

Sorry for the misinformation.

MartinSchmidt Mr or Mrs. 500 Points: 525 More actions · Answer 10

totally agree with "Mr or Mrs. 500"

If you'r running 2008, you could also have a look at CDC!

Animal Magic SSChampion Points: 13964 More actions · Answer 11

mwebster (7/31/2009)
Doh, thanks for letting me know. Since this is in the SQL 2005 section, no help then.
Sorry for the misinformation.

sorry i wasnt pointing it out as misleading info, i was quite interested in getting a copy myself if it was free and then saw that note so just thought i would add it to the post for completeness.