Fetching Last inserted record in SQL table

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 ?
Faraj1969Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Faraj1969Author Commented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Patrick MatthewsCommented:
Hello Faraj1969,

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

Regards,

Patrick
0
 
kulromCommented:
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
 
kulromCommented:
Uh pardon;

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

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

Regards :)
0
 
kulromCommented:
Anyway the matthewspatrick's solution is more elegant. I just wanted to give you an alternative.

0
 
j-w-thomasCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
RejojohnyCommented:
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
 
j-w-thomasCommented:
angelIII, of course you are correct...just a little cross-referencing slip-up...
0
 
Faraj1969Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Faraj1969Author Commented:
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
 
Faraj1969Author Commented:
Many thanks Angellll, that worked as I desired.

Cheers
0
 
kulromCommented:
***************************************************************************
"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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.