I need the highest ID from database. Am I doing this right?

Question

    set qv = createobject("adodb.recordset")
q ="select * from tbl order by ID"
qv.open q,QuoteConn,3,1,1
 qv.movelast
 qid=qv("ID")
qv.close

EDIT:

awwww, y'all killed the joke. In year 2009, I am maintaining this piece of code...

Answer 1

what about MAX(id) ?

Answer 2

A quicker way would be

q="select max(ID) from tbl"

This will always result in a single row with one value (with the highest id).

Answer 3

If you're feeling wacky you can also reverse the SQL order and select the top one.

Answer 4

I don't know your particular flavour of SQL, but in many you can do that as:

q="select * from tbl where ID = (select max(b.ID) from tbl b)"

Answer 5

Yes, but in the slowest way possible.

More than that, your premise is wrong. You don't need to get the highest ID from the database.

My psychic debugging powers tell me you're going to use this to create a new ID and insert that ID back into the database with a new record. That's entirely the wrong way to go about it, and will lead to race conditions in that code on your web site. Instead, set up your ID as an Identity column and let Sql Server create your ID's automatically. Then select new ID's back into your results after the insert using Sql Server's scope_identity() function.

Even if I'm wrong and you just want to use it to retrieve the most recent record, what you really want is likely something like scope_identity() (if you just created the new record as part of the same session) or ident_current(tbl) (if the new record was perhaps created by someone else or created some time ago). And in this last case, you really ought to have some other way than the ID to determine what's "most recent".

Answer 6

Other answers are correct, I'd just suggest you give it an alias so you can access it:

select max(ID) as "maxID" from tbl
...
qid = qv("maxID")