Fetching Last inserted record in SQL table

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?

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

Hello Faraj1969,

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

Regards,

Patrick

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 :)

Uh pardon;

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

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

Regards :)

Anyway the matthewspatrick's solution is more elegant. I just wanted to give you an alternative.

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.

https://www.experts-exchange.com/questions/20890178/last-insert-id.html?sfQueryTermInfo=1+get+id+insert+last+row

John

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 ...

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

angelIII, of course you are correct...just a little cross-referencing slip-up...

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

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

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

Many thanks Angellll, that worked as I desired.

Cheers

***************************************************************************
"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 :)