How to go to the previous record with non consecutive ID numbers

[usr123]

I want to get the previous record based upon ID which is PK of the table.
Users are allowed to delete records from the table.
Problem:
Coz ID is autonumber,ever y time new record is created,it generates a new ID number for the record.When user delets that particular record, that ID number doesnt get deleted. When user enters new data, ID generats next available number and doesnt reuses the ones from where record has been deleted.
Now my problem is,i need to show previous record on the form.I can show last one based upon Max(Id). AFter that how would i calculate ID to pick up the second last record and so on....

I am really stuck, any help is appreciated.

Thanks

[Delerna]

Ahhh, that's the problem with auto numbers

Here is one way if you are using SQL server 2000
Make a view that returns two columns
The ID and the PreviousID
[code=sql]
SELECT a.ID,(SELECT MAX(ID) FROM TheTable b WHERE b.ID<a.ID) as PrevID
FROM TheTable a
[/code]

Be warned...this could be quite slow if you have a lot of records because the subquery gets executed over and over for every record in the main query.
So, if there are 10,000 records in the main query then the subquery gets executed 10,000 times.
However, since ID is a PK it will be indexed and therefore it will take a LOT of records to make it slow.


SQL Server 2005 and up has a better way.
It has a fuction that returns the record number of a query.
You could use that instead of ID because it WILL be sequential.