Retrieve last record of SQL Server table

Question

Here is the my problem: I have a SQL Server database called emp. It has an employee table (with userid int column). I need to retrieve the last record of the userid in employee table with increment userid value + 1. At the moment I did it on the my GUI. So how do I write a sql query for it?

Answer 1

To return the the record with the highest userid, you can do:

SELECT TOP 1 userid
FROM employee
ORDER BY userid DESC

or...

SELECT MAX(userid)
FROM employee

If your plan is to then increment the userid manually and insert a new record with that new ID, I'd recommend against doing that - what if 2 processes try to do it at the same time? Instead, use an IDENTITY column as userid and let the incrementing be handled for you automatically

Answer 2

You shouldn't be manually incrementing the userid column, use an IDENTITY column instead. That will automatically add 1 for you for every new row.

CREATE TABLE Employees (
    UserId INT IDENTITY PRIMARY KEY NOT NULL,
    UserName NVARCHAR(255) NOT NULL,
    // etc add other columns here
)

If you really really have to select the highest userid it is a very simple query:

SELECT MAX(UserId) + 1
FROM Employees

[Edit]

Based on your comments, you should use the SELECT MAX(UserId) + 1 FROM Employees query. But be aware that this does not guarantee the number will be the ID. Normally you would not show an Id value until after the record has been saved to the database.