By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,324 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

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

P: 20
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,every 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
Feb 4 '10 #1
Share this Question
Share on Google+
1 Reply


Delerna
Expert 100+
P: 1,134
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
Expand|Select|Wrap|Line Numbers
  1. SELECT a.ID,(SELECT MAX(ID) FROM TheTable b WHERE b.ID<a.ID) as PrevID
  2. FROM TheTable a
  3.  
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.
Feb 7 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.