MAX - Selecting a Maximum Number in SQL

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.

Introduction to ASP Ebook

Download this ebook to get everything you need to know about learning ASP - from a step by step tutorial to function lists, sample code, common errors and solutions, and much more! 101 pages.

Related Articles
Editor's Picks Articles
Top Ten Articles
Previous Features
Site Map

Content copyright © 2023 by Lisa Shea. All rights reserved.
This content was written by Lisa Shea. If you wish to use this content in any manner, you need written permission. Contact Lisa Shea for details.