?
Solved

Fetching Last inserted record in SQL table

Posted on 2007-10-02
16
Medium Priority
?
1,045 Views
Last Modified: 2008-08-24
I have an SQL table with some records, each record have DateTimeStamp field as string, how can I find out the last record inserted so I can collected that record fields ?
0
Comment
Question by:Faraj1969
  • 4
  • 4
  • 4
  • +3
16 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19997206
several questions:
* does the table have an identity field?
* do you want the last record inserted overall, or from your instance of the application?
* >DateTimeStamp field as string<  WHY THAT? why not datetime (much easier)?
* >DateTimeStamp field as string<  what format, anyhow?

0
 

Author Comment

by:Faraj1969
ID: 19997280
Several ansers:
* Yes it does, its the auto numbering.
* If I get the last inserted record overall, I can filter it and get what I need.
* I faced problems when I insert datetime specialy for logon and logoff time, so I'm using them as string.
* dd/mm/yyyy hh:mm:hh

Cheers
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19997290
Hello Faraj1969,

SELECT *
FROM YourTable
WHERE ID = (SELECT MAX(ID) FROM YourTable)

Regards,

Patrick
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Expert Comment

by:kulrom
ID: 19997326
Or simplier

"SELECT MAX(ID) As lastRecord FROM YourTable"

Or

"SELECT TOP 1(ID) As lastRecord FROM YourTable OrderBy ID DESC"

both will return the same result which is a  last record in the table


Regards :)
0
 
LVL 1

Expert Comment

by:kulrom
ID: 19997347
Uh pardon;

SELECT anotherField, secondField, MAX(ID) As lastRecord
FROM YourTable
GROUP BY anotherField

This will work ... please ignore my previous reply.

Regards :)
0
 
LVL 1

Expert Comment

by:kulrom
ID: 19997352
Anyway the matthewspatrick's solution is more elegant. I just wanted to give you an alternative.

0
 
LVL 3

Expert Comment

by:j-w-thomas
ID: 19997400
There are several ways to do this depending on how you want the info:

SELECT * FROM TABLE1 ORDER BY DATE1 DESC LIMIT 1

This as you may see creates the result set and takes only the first row of the results and since the date and time will be the most recent, then you will get the last record.

I have not had time to test this but I think that MAX() will work too, but it will involce two trips to the db one to retrieve the last record and then one to get the actual record.

SELECT MAX(DATE1) FROM TABLE1

then

SELECT * FROM TABLE1 WHERE DATE1 = '& variable1 &'

The variable wouldd be the value from the first select.

You probably have an auto-generated ID number in the table as well and you can use MAX() on that one too and then select using the ID number.

Also, depending on if you want the info immediately after the last record was inserted, this question may help.

http://www.experts-exchange.com/Database/MySQL/Q_20890178.html?sfQueryTermInfo=1+get+id+insert+last+row

John
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19997724
just a remark: LIMIT 1 is for MySQL, the question is posted with SQL 2005 information ...

hence:
SELECT TOP 1 * FROM TABLE1 ORDER BY CONVERT(datetime, DATE1, 103) DESC
resp:
SELECT TOP 1 * FROM TABLE1 ORDER BY ID DESC

now:
* I faced problems when I insert datetime specialy for logon and logoff time, so I'm using them as string.
can you explain that? using the vb.net's DateTime.Now or SQL 's Getdate() function give you the correct datetime value ...
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 19998946
Several ansers:
* Yes it does, its the auto numbering.
* If I get the last inserted record overall, I can filter it and get what I need.

If there is a identity column, scope_identity gives you the last inserted record .. you will need to check that immediately ater an insert statement ..

insert into table ...
set myId = scope_indentity
you can then select row by writing the query .. select * from table where id = myid

Rejo
0
 
LVL 3

Expert Comment

by:j-w-thomas
ID: 19999930
angelIII, of course you are correct...just a little cross-referencing slip-up...
0
 

Author Comment

by:Faraj1969
ID: 20005057
Hi again,
OK, basicaly I'm confused, though I'm very thankful. but what I need is a select statement to find the last inserted record (last insererted record can be yesterday, or anytime), and also to retrieve the info of that record to put it in dataset. And I prefer the idea of using the identity field which is called "RegisterKey" rather than using the confusing Date thing.

Cheers
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20005078
you still owe us if you are working on MySQL or MS SQL...

in MS SQL Server, I posted already the syntax:
SELECT TOP 1 * FROM TABLE1 ORDER BY ID DESC

in MySQL, it would be:
SELECT * FROM TABLE1 ORDER BY ID DESC LIMIT 1
0
 

Author Comment

by:Faraj1969
ID: 20005119
Ummm.. Angellll, if you look at the original question above, it says clearly in the zone area MS SQL 2005.... ;)
Can I use the statement like this ?
SELECT TOP 1 * FROM Table1 WHERE (IndCode = '.......') ORDER BY ID DESC
cause I need to specify WHERE as another condition.
Cheers
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20005152
>Ummm.. Angellll, if you look at the original question above, it says clearly in the zone area MS SQL 2005.... ;)

Actually, that was due to the comment of j-w-thomas who posted the MySQL syntax ...
sorry

>Can I use the statement like this ?
>SELECT TOP 1 * FROM Table1 WHERE (IndCode = '.......') ORDER BY ID DESC
>cause I need to specify WHERE as another condition.

yes, no problem. the WHERE will be applied first, then order by and finally the TOP
0
 

Author Comment

by:Faraj1969
ID: 20005218
Many thanks Angellll, that worked as I desired.

Cheers
0
 
LVL 1

Expert Comment

by:kulrom
ID: 20005459
***************************************************************************
"SELECT MAX(ID) As lastRecord FROM YourTable"

Or

"SELECT TOP 1(ID) As lastRecord FROM YourTable OrderBy ID DESC"

both will return the same result which is a  last record in the table
***********************************************************************************

That's pretty same solution with the one i offered ... just i missed to select ALL that is not good idea anyway.
However, i am glad that your problem was sorted out :)

0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question