How to get last record / max value

Question

I have PWD which should go like 0001, 0002, 0003, 0004

enter image description here

So when i want to add the new record in database i get the max value like this

sql.Query(" SELECT pwrd FROM users WHERE pwrd = (SELECT max(pwrd ) FROM users)")
        Dim highest As Integer = (sql.SQLDataset.Tables(0).Rows(0).Item(0))

On this way i get the highest row from table example "0004" Now when adding the new query i need to increment that value for 1 to be "0005"

If i go TextBox1.Text = Highest + 1 i will get 5 only without "000"

I'd either use an auto increment column type, or set this value during an insert trigger. Trying to do it in code like this is going to lead to race conditions. — asawyer
What do you mean under insert trigger to do autoincrement. How would that query go — Anel Hodzic

Rahul Tripathi Rahul Tripathi · Accepted Answer · 2015-04-17T18:11:40

You probably want to add ZEROFILL attribute to the field.

You can create your table like this:

ALTER TABLE users MODIFY pwd INT(3) zerofill AUTO_INCREMENT not null;

EDIT:

You can try to drop your table and create like this:

CREATE TABLE users
(
   ID INT IDENTITY(1, 1)
   , pwd  AS RIGHT('000' + CAST(Id AS VARCHAR(4)), 4)
  )

How to get last record / max value

2 Answers