Keeping results in order by ordering by ROWID

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Keeping results in order by ordering by ROWID

Author

Topic

eddy556
Starting Member

36 Posts

Posted - 2009-03-24 : 10:29:14

Is there a way to keep records in order in MSSQL 2005. I thought I had solved this by setting Identity Specification to Yes and then ordering results via that column.

However as records are updated etc they become out of order.

Unless there is another way how would I increment a ID column by 1 in SQL?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-03-24 : 10:32:02

unless you are updating the ID number, they should not be "out of order".

What are you using as criteria for being "in order"?

Date created? Date updated?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

eddy556
Starting Member

36 Posts

Posted - 2009-03-24 : 10:33:26

Sorry just to clarify I beleive the reason the records are becoming out of date is due to old records being removed and then their replacements being INSERTed therefore adding it to the bottom of the list. Any ideas of how I can work my way around this?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-03-24 : 10:39:36

order has NO MEANING in the database. put them in order when you take them out.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

eddy556
Starting Member

36 Posts

Posted - 2009-03-24 : 10:42:27

I know this however I receive data from a data service in a particular order. These are inserted in the database - and their order needs to remain intact. Therefore there must be a field which provides the ordering information so I can therefore ORDER BY rowid etc. This is my question.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-03-24 : 10:47:14

So, to see if I understand.

You have your database, and there is an IDENTITY field in the table.
You insert records. You delete records. You insert REPLACEMENT records.

How are these REPLACEMENT records inserted? Are they simply updating older records?

If this is the case, you may wish to have a "Date_Inserted" column to order by.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

eddy556
Starting Member

36 Posts

Posted - 2009-03-24 : 10:50:19

Yes thats right. These replacement recorded are INSERTed. This is the problem I cannot think of a way around this.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-03-24 : 10:55:25

So, the replacement records are NOT UPDATING old records, but are INSERTS, just like new records?
I don't see how they could be out of sync with the IDENTITY field.

I get the feeling I am missing something, but don't know what to ask you. Follow the bold link in my signature, and restate your question with some sample data and output.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

eddy556
Starting Member

36 Posts

Posted - 2009-03-24 : 11:06:06

Here is an example:

ROWID ------------ Record
1 Dog
2 Cat
3 Mouse

Dog no longer exists when comparing to the web service and so is deleted like so:

ROWID ------------ Record
2 Cat
3 Mouse

Then a replacement record come along which *should* be in the same place as Dog. This is puppy. However because it is an INSERT (I cannot simply update dog as it is already gone + we don't know this is actually updating dog - it may simply be a completly new record but which needs to be put in the same place) it is moved to the bottom like so:

ROWID ------------ Record
2 Cat
3 Mouse
4 Puppy

I hope you can understand the problem.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-03-24 : 11:36:57

I see what you are saying now. Why is the "Dog" record deleted without the replacement record "puppy" waiting to take its place?

Instead of doing a physical delete, you might want to do a logical delete.

When you say "Replacement record", i can't help but think UPDATE.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources