bluemunch bluemunch - 1 year ago 46
SQL Question

Find a value based on the most recently created row in a table (SQL Server)

This potentially might be too large of a question for a complete solution, and I've got a bit of a strange set up. I'm using HP OO to create a text-based RPG just to practice getting used to database design on this platform.

So it's basically a flow script that runs once. When the script starts, a player (user) is created, and then a character is created. The player inputs a name for its character, and this is stored in the

table. I then call that character name with
SELECT name FROM character WHERE character.character_id=x
. How can I retrieve the name from the correct (most recently created) character. The
is an auto-incrementing identity column.

Answer Source

There's nothing guaranteeing that the highest value in an identity column is the most recently created record. You should add a date_created column to your table and give it a default value of the current date and time (current_timestamp for a datetime2 field). That actually does what you want.

OK, your question changed a bit and, Tab's comment here is also correct. If you want to insert and get the identity inserted back, you should follow the advice here that he linked.

However, if you want to be able to determine the order of creation -- which is what you originally asked -- then you should use a date_created field. It's possible to get around IDENTITY and insert any value you want, and things like UPDATEs and DELETEs can change things as well. Essentially, it's a bad idea to assign meaning to a record's value of an IDENTITY column relative to other records in the table (i.e., this was created before or after these other records) because you can actually get around that.

Personally, I would either use the OUTPUT clause to have my INSERTs send the ID back:

INSERT INTO Character (...)
VALUES (....);

Or I'd reuse the same connection and return the SCOPE_IDENTITY().

INSERT INTO Character (...)
VALUES (....);