Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 12th, 2005, 05:49 PM
Registered User
 
Join Date: Apr 2005
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Insert into & order by...

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

Reply With Quote
  #2 (permalink)  
Old October 12th, 2005, 08:05 PM
Registered User
 
Join Date: Apr 2005
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #3 (permalink)  
Old October 12th, 2005, 08:53 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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....

Reply With Quote
  #4 (permalink)  
Old October 12th, 2005, 08:57 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #5 (permalink)  
Old October 13th, 2005, 01:21 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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)
Reply With Quote
  #6 (permalink)  
Old October 13th, 2005, 05:06 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by joefawcett
 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.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Updating products quantity when order item insert Tawanda BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 1 June 7th, 2007 02:30 PM
SELECT/ORDER/INSERT Stuart Stalker SQL Server DTS 1 July 24th, 2005 01:00 AM
* where date >= '"& dateVar &"' order by date wrofox Classic ASP Databases 5 February 24th, 2005 10:11 AM
INSERT & SELECT FROM....? No idea Seb_soum Classic ASP Databases 3 May 14th, 2004 07:06 AM
SELECT and INSERT in wrong order sgarstin MySQL 0 October 16th, 2003 02:31 PM



All times are GMT -4. The time now is 03:12 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.