How to Select last row inserted

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

Transact-SQL (2005)

How to Select last row inserted

Author

Topic

kallileo
Starting Member

21 Posts

Posted - 2007-05-08 : 04:03:03

How can I select one cell of the last inserted row?

Is it has something to do with the @@IDENTITY function?

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-08 : 04:10:26

@@identity will be useful if the table has identity column

otherwise you require a additional column which stores the date when the row is modified. using that we can take the required value.

Kristen
Test

22859 Posts

Posted - 2007-05-08 : 04:34:06

If your table has a column with the IDENTITY attribute you can find the allocated Identity value immediately after your insert.

Note that you should use SCOPE_IDENTITY() for this, rather than @@IDENTITY


INSERT MyTable(MyCol1, MyCol2) VALUES (123, 'ABC')

SELECT SCOPE_IDENTITY() AS [IDENTITY]

Kristen

kallileo
Starting Member

21 Posts

Posted - 2007-05-08 : 04:44:40

No I don't have Identity column but I have a datetime column...maybe I could it to identify the last row...

Kristen
Test

22859 Posts

Posted - 2007-05-08 : 04:59:59

[code]
SELECT MyCol1, MyCol2
FROM MyTable
WHERE MyDateCol = (SELECT MAX(MyDateCol) FROM MyTable)
[/code]
That assumes that MyDateCol will be UNIQUE ... otherwise you will get multiple rows coming back. If you don't care WHICH row you get back then:
[code]
SELECT TOP 1 MyCol1, MyCol2
FROM MyTable
WHERE MyDateCol = (SELECT MAX(MyDateCol) FROM MyTable)
ORDER BY MyPKCol1, MyPKCol2 -- Order by Primary key for repeatability!
[/code]
Kristen

kallileo
Starting Member

21 Posts

Posted - 2007-05-14 : 13:22:40

I have a table with values like this:

name__rawvalue___quality___timestamp
s1______-1_____good____12-6-2007 9:45:00
s3______-1_____bad_____13-5-2007 11:50:51
s1______0______good____14-7-2007 8:40:54
s2______0______good____15-5-2007 6:00:45

Then I execute the query for s1
SELECT [rawvalue] FROM [datastore] WHERE timestamp=(SELECT MAX(timestamp) FROM [datastore]) AND name='s1'

I get correct rawvalue = 0

but then I execute the query for s2
SELECT [rawvalue] FROM [datastore] WHERE timestamp=(SELECT MAX(timestamp) FROM [datastore]) AND name='s2'

I get nothing because the Max(timestamp)= '14-7-2007 8:40:54' 3rd row and the name is "s1".

How should I modify the query to get correct rawvalues for every s1,s2,s3......

I tried:
SELECT [rawvalue] FROM [datastore] WHERE timestamp=((SELECT MAX(timestamp) FROM [datastore]) AND name='s2')

But I get error in syntax.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-14 : 13:45:54

You should change the query accordingly for name='s1' and name ='s2', depending on your INSERT. Since your INSERT will have all the values you can use them to retrieve the record.


INSERT INTO ()
VALUES (@param1, ...)

SELECT rawValue
FROM <table>
WHERE timestamp = (SELECT MAX(Timestamp) FROM table WHERE param1 = @param1 and param2 = @param2...)

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

kallileo
Starting Member

21 Posts

Posted - 2007-05-14 : 16:39:05

I don't understand exactly what you wrote above....but I can't change nothing in the INSERT command bc it I have a program that is connected to a hardware device and inserts in the table only when a state of a input/output changes.

It inserts the "name", rawvalue(integer or boolean), quality(never changes) and timestamp.
The only parameters I can use to identify the reord I need are name and timestamp.

kallileo
Starting Member

21 Posts

Posted - 2007-05-14 : 17:15:36

OK. I think I found it.

SELECT [rawvalue] FROM [datastore] WHERE timestamp=(SELECT MAX(timestamp) FROM [datastore] WHERE name='s1')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources