automatic ordering of rows - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

automatic ordering of rows

Author

Topic

ujjaval
Posting Yak Master

108 Posts

Posted - 2005-12-19 : 19:40:02

I am using SQL Server 7. I created a table with following query

create table [dbo].[Order](
[OrderID] [int] NOT NULL,
[CustomerID] [varchar] (5) NOT NULL,
[OrderDate] [varchar] (8) NOT NULL,
)
ON [Primary]

Alter table [dbo].[Order] with nocheck add constraint [PK_Order] primary key clustered
(
[OrderID]
)ON [PRIMARY]
go

The OrderID in rows are 10001,10002,10003 respectively. Now when I update the orderID 10001 to 10005 and then retrieve all three rows, it displays the row with ID 10005 as last row. Is this done automatically??

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-19 : 21:40:43

Hi ujjaval

the order of the rows returned by a query is purely coincidental unless you specify an "order by" clause in your SQL. In the case you've described, the reason the update row is returned last is probably related to that row being most recently updated - but you should not rely on that ordering. if you requeried the table later, you may get it in a different order...

ie you should always specify an order if it is important

select * from dbo.[Order] order by orderid

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-19 : 23:45:49

As said, SQL Server will display data that is not in any particular order until you tell it to order by the specific columns

Madhivanan

Failing to plan is Planning to fail

ujjaval
Posting Yak Master

108 Posts

Posted - 2005-12-20 : 18:35:26

What if I don't want my data in any order. What should I do for that??

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-20 : 18:38:57

in that case you don't care what it does...? or am i missing something?

of course, if you want your rows in random order - try this

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

ujjaval
Posting Yak Master

108 Posts

Posted - 2005-12-20 : 18:48:25

Yes story is a bit different. The scene is like this.

I have three tables.
1. Order
2. OrderDetails
3. TrailOrders

Table 'Order' has a primary key 'OrderID'. This 'OrderID' is a foreign key in other two tables. I have removed the foreign key constraint on these tables and I am using trigger to perform cascade update on this column.

Now, in cascade update only one row at a time is possible to update in primary table. So, I am using a loop which selects nth row in order from 'Order' table, updates its OrderID and also in the other two tables.

Now, the 'OrderID' column was int type before and SQL has tendency to sort the rows based on this automatically. I changed its type to varchar. But it is still sorting the rows automatically. Now, as you know that I am taking one row at a time and doing update, after each update it sorts the table again and I get incorrect rows based on the previous updates. So, I don't want SQL to sort it automatically.

make sense?? or if you have any other solution for this whole thing, I really appreciate that.

Thanks,
Ujjaval

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-12-20 : 18:49:21

If you don't care what order, then leave off the order by.
If you need a random order, do a
ORDER BY NewID()

I'm pretty sure that works in SQL 7.

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

ujjaval
Posting Yak Master

108 Posts

Posted - 2005-12-20 : 19:00:39

I don't need that in random order. I just need them in order as all the data were before any updates.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-20 : 19:04:10

quote:
I just need them in order as all the data were before any updates.

then you need to record what order they were in before any updates. eg use a timestamp as they are created, or assign an ordering value before they're updated and order by that

the point is that by definition there is no "default" order in a relational database. if you just do

select * from table

you can potentially get a different order every time you do it. In most cases you will get the same order, but this is not guaranteed unless you specifically use the "order by" clause.

does that help?
--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

jhermiz

3564 Posts

Posted - 2005-12-20 : 19:41:47

I cant believe you know how to create DDL CREATE TABLE statements but you dont know the ORDER BY clause ?

That is strange

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

ujjaval
Posting Yak Master

108 Posts

Posted - 2005-12-20 : 19:55:33

Thank you all for your kind replies.

Yes it suprized me also that I didn't realize the ORDER BY statement. :-) My bad. But yes, its all good now. my cascade update is working :-)

thanks heaps again.

ujjaval

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-20 : 20:00:20

good luck

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources