Hi all,

In a stored proc i need to INSERT the results of a SELECT xxxx ORDER BY timestamp into a table and have the results be in the same order as the ORDER BY.

So basically...
INSERT INTO tblResults
SELECT Name, Timestamp
ORDER BY Timestamp

Now at this point i was hoping the rows in the tblResults would be in chronological order... but they are not???!!??

I need to do this as i then need to add a squential number to the results table which i do with...
-- Add sequential number based on ORDER BY above.
DECLARE @intCounter INT
SET @intCounter = 0
UPDATE tblAuditResults
SET @intCounter = IN_SEQ = @intCounter + 1

This works, but only if the rows are in the correct order first.

So i want to end up with a table with items in chronological order based on Timestamp & with sequential numbers.

Q: How do can i get the INSERT to populate the tblResults with row in chronological order???

Q: Is there a better way of doing this all together??

Cheers,
Ken

and for those who are interested the answer is...
Add a clustered index to your table on the "timestamp" column before you run the UPDATE statement.
thanks to Rob Volk for that.

In situations where you can't add an index you could also use a cursor or temp table if there is not that much data, and clustered index the temp table. Your solution assumes you don't already have a clustered index on your table. Try putting two clustered indexes on the same table, hehehe....

I'm not trying to start trouble, but I need to jump in to question the whole premise here.

A table is a set of rows. A set, by definition, is an unordered collection of elements. Of course, one can always construct a resultset by ordering the rows of a table by one or more columns in that table, but the table itself is unordered.

Thus, I submit that trying to construct a table with the rows in some predetermined order misses the whole point of a relational database.

It is true, in SQL Server anyway, that all other things being equal, the resultset of a SELECT statement with no ORDER BY clause on a single table, by default will be returned in the order of the clustered indexed on that table. But this takes advantage of some prior knowledge of the way the query processor chooses to process a query. There is no requirement that the rows of a query, absent any ORDER BY cluase, return the rows in any order at all. Thus the query processor is free to return the rows in any order it wants to. Any number of things may in fact influence the processor - it's goal, after all, is to return the correct result as fast as possible.

If you want a resultset to be in a particular order, specify it via an ORDER BY clause.

This is why, by the way, you cannot specify an ORDER BY clause when you define a view. A view is a (virtual) table, and as described above, tables are inherently unordered.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I agree with Jeff about the unknown nature of the row order.
My one point is that if you want a view that always delivers records in a particular order you can add an ORDER BY but you also need to use SELECT TOP 100 PERCENT before the actual selection.

--

Joe (Microsoft MVP - XML)

It's not so much that the ordering is unknown, as it is that there isn't any.

I stuck that comment about views in my post wondering if I would elicit this TOP 100 PERCENT ... abomination.

If you want a query that returns its rows in a particular order, say so by specifying an ORDER BY clause. It can be really cheap, if the query processor was going to return the rows in that order anyway (via a clustered or other index).

But a view is really just a virtual table. Thus, I am free to JOIN the view to other views, tables, and/or other queries (derived tables). If I have specified an ORDER BY clause via the (nonstandard) TOP clause in the view, then this ordering will most likely result in suboptimal behavior, since the query processor is forced by the view definition to construct the JOIN in ways that may not be able to take full advantage of other information the query processor has, such as indexes, data distribution statistics, etc.

Once I start specifying JOINs in a query, I'm sure you would agree that all bet's are off regarding any inferences I can make about the ordering of rows (not that I should be making any anyway :D). If you have JOINs, and you need the results in a particular order, you must specify an ORDER BY clause. Given that, any ordering I have imposed in the view will be performed because of its definition, then destroyed due to the JOIN operations.

I believe that if you think you need to order a view, you don't understand what they really are.

Tables aren't ordered, resultsets are (or may be).

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com