MAX - Selecting a Maximum Number in SQL
SQL provides an easy to use method of determining the largest, or maximum, number in a database. This can get you the largest salary, next available ID number, and much more.
The function is called MAX and it can be used on any numeric field. Let's say you have a table that has one column called writer_id and another column called writer_name. You want to insert a new writer into that table, but you need to get a new ID # for this new writer. So you first need to know the current highest writer's ID number and increment that by one. You would do a SQL query saying
select max(writer_id) as maxid from writers;
The reason that you use the "as maxid" in your SQL syntax is that when you then go to do something with the result, you need a name by which to refer to this value. If you had just said
select writer_id from writers;
you could then say
WriterID = objRec3("writer_id")
but if you're using a function, it's not so easy. You don't have just a column name you want to do something with - you have a result of a function. So you have to give it a name.
So in our above case, using
select max(writer_id) as maxid from writers;
you would then say
WriterID = objRec3("maxid")
and then you'd say
NewID = WriterID + 1
and voila! You would have the next ID to do your new insert with.
SQL Command Listing