Retrieve the last record in a database which has a alphanumeric key

I have a database which stores video game details.The table name is games & its primary key is 'gameNo', which is alphanumeric(ex. G1,G2,G3.... etc.). I want to retrieve the last entered game's number in order to generate the game number for the next game, when it is entered. When I use,

this.cmd = new OleDbCommand("SELECT MAX(gameNo) FROM games",this.con.conObj());

i get incorrect results开发者_开发技巧. For example if there are gameNo values like G2 & G190, I want to retrieve G190, instead I get G2. Please any help would be greatly appreciated. I'm a student & if possible a good explanation would be a huge help too. Thanks in advance. By the way i'm using Visual C#.

---

This is a consequence of storing the key as a varchar column.

I would separate out the 'G' from the Number and have two columns. If it is always 'G' then you dont even need the two columns.

Also you have another problem which is caused by using the SELECT MAX(gameNo) FROM games to get the last game number. If this system is a multi user system then two users could get the same number. You didn't mention which database you where using but all databases will let you safely get a unique id for a game.

I would have a table something like (This is for SQL Server but you will get the idea.

CREATE TABLE Games(
    GameID int IDENTITY(1,1),
    ...Other Columns,
    CONSTRAINT PK_Games PRIMARY KEY CLUSTERED(GameID))

Then when you create the new row for the game the database will handle giving you a new GameID.

---

To be honest, while there is definitely a solution to this that parses the gameNo in the query, I'd strongly suggest changing the database model to use a proper auto-incrementing primary key using integer data type instead.

1. An alphanumeric primary key reeks of bad design, in my opinion.
2. You completely circumvent the need to generate the primary key yourself by using the appropriate mechanism the database is equipped with.
3. You avoid "race conditions" if there are multiple game entries being created at the same time, which would then result in a PK violation for one of the processes that tries to insert the entry.

---

There's a couple of issues here. One is just because gameNo is alphanumeric doesn't mean the last record is the largest value. You'll have to detail how the values in this column are auto-incremented The other issue is what is "largest". With alphanumeric the values are compared as test, not numbers. So, '3' would come before '1a'.