select MAX pk in database, but whole record? 2

[Ovatvvon]

I want to select the most recent record in the database and use that for the object rs to work with the .asp page. I don't want to select the whole recordset and just rs.movelast because that's more time taken for somthing that could go much faster by just one record selection which is all we need...

I tried:
sql = "SELECT MAX(locals.localPK) FROM locals;"

but that only returned the localPK field. Is there a way to select the maximum primary key field, but retrieve the entire record associated with that field?

Thanks in advance! -Ovatvvon :-Q

 

[link9]

SELECT * FROM locals WHERE localPK = (SELECT MAX(locals.localPK) FROM locals)


paul

 

[Ovatvvon]

Ahh, great...I had tried a similar solution to that except I didn't enter the second Select within the where clause...only the MAX(locals.localPK).

Thanks. Will try that when I go to work on monday. -Ovatvvon :-Q

 

[link9]

Remember this one, too. It's not a terribly efficient query (i.e. you might not want to use it in a production application or anything), but it's great for retrieving information for your own use:

SELECT * FROM someTable WHERE pk IN
(SELECT pk FROM someOtherTable WHERE someColumn = someValue)

can return many records (depending on the results of your subquery)

You can even use the NOT in front of it like:
SELECT * FROM someTable WHERE pk NOT IN
(SELECT pk FROM someOtherTable WHERE someColumn = someValue)

also capable of returning many records.

It basically does a full table scan on both tables, which is where the inefficiency lies, but like I said, great query for trying to track down inconsistencies, or whatever.

Just remember you can't specify more that one column name in the subquery, or it won't work.

Subqueries are your friend. ;-)
paul