Question about record numbers

[bminaeff]

Hi all,

I have a question about whats the best way to keep track of a records number. I am probably going to phrase this poorly, so thanks for sticking with me.

Lets say I have 5 records. I insert them to a table and one of the columns in my table is a record number, so values in this case will be 1,2,3,4, and 5. If I want to delete record 3 and make record 4 -> record 3 and record 5-> record 4, is there an easy way to do this?

Or is there a way if I know I have a table with 5 records in it to get say the 2nd or 3rd record?

Thanks in advance.

 

[kjv1611]

I suppose what you're talking about is basic database design. You want to have an ID field for each record, so the table can be indexed, searched more efficiently, etc.

But what you're saying about wanting to change the data in that field to match sort of "cover up" any deleted records is not something that would be a good idea, in my opinion.

The reason is that if you ever need to look back at historical data from that table, and you want to query by that ID field in your table, you're going to get totally different records.

Is it possible to change a numeric field? Of course it is - programatically. But it just doesn't seem to make good sense.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SQLSister]

An additional reason for never changing the id field is that any related tables would also need to change or you will introduce data integrity problems.

however, using existing id numbers (which if they are identity fields (they autogenerate the numbers) you can tell what order the records were inserted which may be what you need. There is no easy way to do this except for the first and last (min and max) but you could throw the data into a temptable that also has an identity and then select record #2 or #3 from that.

create table #temp (my_id int, order_id int identity)

insert #temp (my_id)
select top 10 my_id  from my_table order by my_id

select p.* from #temp t join my_table p on t.my_id = p.my_id where order_id = 2

Note it is critical to order the select statment when inserting into the #temp table or you can't guarantee the records will be inthe same order as the records were orignially inserted. If you want the 2nd from the last record, you would order by the idfiled in desc order.

"NOTHING is more important in a database than integrity." ESquared

 

[sblocher]

Database design aside, if you define the table with an Identity field (say you name it RECNO) with an increment of 1, each inserted row will get numbered sequentially as in your example. Just make sure they are in the order you want them as you insert them.

A value in RECNO will never change, unless you change it. If you "delete record 3" as you say, there will no longer be a record with RECNO = 3.

If you want the "first 3 records" from the table, then you can use "...TOP 3...ORDER BY RECNO" to do that. You can return the RECNO field to know what "original" record number it was, and you can use ROW_NUMBER() in your query to assign each row in the result set with a "dynamic" record number in context of the query.

And if you wanted "records 10 thru 20" based on the current data, then use a derived table query with ROW_NUMBER(), like this:

select * from (
select top 20 ROW_NUMBER() OVER(ORDER BY RECNO) AS 'CurrentRECNO', * from table Order by RECNO ) DerivedTable where CurrentRECNO between 10 and 20

If you REALLY want to renumber the RECNO after you delete rows, you can do that. But it is tricky, and as the other posters point out, you don't want to do that if you are using it as a foreign key to other tables.

Not sure this is what you're looking for, but perhaps some ideas.