How can I get the last record?

Hi guys,

Greetings!

I would like to know the last record from my table let say "Inventory" What I want to do is to get the record from a certain field "referenceNumber" This field is in the form of "YYYY-N" "2007-1" .

If I can find the last record I can then increment it by one like "2007-2" will be the next alloted referenceNumber...

How can I do this? Please give me a code starting from the connection... I'm very new to this technology.

Thank you very much to those who will respond.

Regards,

Please help... please...

How to get the last record...

Regards,

|||It sounds like you are not looking for the "last record" but the largest "referenceNumber"

Try:

SELECT MAX(referenceNumber) FROM Inventory|||do you have a datetime filed or a identity filed. in that case, you can use max function to get the id of the table. then you can get the record/for further help, can you post the schema of your table.|||

MAX(referenceNumber) might not work correctly because referenceNumber seems to be a VARCHAR datatype, from which you would get "2007-10" < "2007-2".

This should work:

DECLARE @.maxYear CHAR(4), @.maxNumber INT, @.maxRefNum VARCHAR(20)
SELECT @.maxYear = MAX(SUBSTRING(referenceNumber, 1, 4)) FROM t1

SELECT @.maxNumber = MAX(CAST(SUBSTRING(referenceNumber, 6, 10) AS INT)) FROM t1
WHERE SUBSTRING(referenceNumber, 1, 4) = @.maxYear

-- This is the latest referenceNumber
SET @.maxRefNum = @.maxYear + '-' + CAST(@.maxNumber AS VARCHAR(10))
SELECT @.maxRefNum AS 'Latest_ReferenceNumber'

-- The following shows the full record on the latest referenceNumber
SELECT * FROM t1 WHERE referenceNumber = @.maxRefNum

Of course, this assumes that you don't have any other columns that can be used the locate the latest record. As you can see, this logic is rather complex and will not perform well if you have a lot of records. I echo with what Dinesh Asanka said - it would be much easier if you have an identity or a datetime column.

|||You are correct, I didn't think about that.

The best solution would be to add an identity column. Just incrementing '2007-1' is going to be a battle and time consuming.