sql Next to last Record

stephenz43
stephenz43 used Ask the Experts™
on
Greeting

I cant figure out a query that retrieves only the next to last record where the account number = some value  ( not the last record inserted into the dbase)

Im using vb 2005 and access

Any idea's ?

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
I think this will work

Select acct_id from [sometableName] where acct_id < [some value] order by acct_id DESC;

then just grab the first record returned and that should be the second to last;

Author

Commented:
hey guidway....

Getting close, but can [some value] be obtained using max[acct_id) ?

steve

Commented:
you could try a subquery

Select acct_id from [sometableName] where acct_id < (Select max(acct_id) from [sometableName]) order by acct_id DESC
Commented:
should be a semicolon at the end

Select acct_id from [sometableName] where acct_id < (Select max(acct_id) from [sometableName]) order by acct_id DESC;

(this was tested in access, not sure which database you are using)

Commented:
oops you said access... sorry. ;)
max function does work in access i believe

Commented:
glad it worked for you. :)

Author

Commented:
thank you for the help...You saved me alot of aggravation

take care

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial