Need help inserting records from another table in a particular order

[DougP]

First of all the requirement is to not have to use Order by when viewing the raw data.
"Select * from tablename" should show them in order as mentioned below.
The table the records are from is sorted correctly by Date Descending and Time Descending. However when doing an insert the records get out of order. I tried adding an Order by in the insert clause but this is not allowed the way I am doing it(see blue code).
ANY help would be appreciated, suggestions ideas or whatever, for constraints or indexes prior to importing; since this is on going? In testing the records were deleted and now I am having trouble getting them back in correctly. I managed to do it once but the notes I had were deleted. I tried adding an index but this did not index them properly by date and time it just left them out of order. Adding the blue script gives error: Expecting ')', EXCEPT or UNION.

Insert into [dbo].[HistData]
([Ticker], [BarSize],[HD_Date],[HD_Time],[HD_Open],
[HD_High],[HD_Low],[HD_Close],[Stock_Volume])

(Select 'ES', '5 mins', CONVERT(date,[HD_Date],101), CONVERT(time,[HD_Time],108),[HD_Open],
[HD_High],[HD_low],[HD_close],0
from [dbo].[Emini5$] [COLOR=blue]Order by HD_Date DESC, [HD_Time] DESC[/color])

DougP

 

[fredericofonseca]

remove the () on the select bit.

but why in hell do you think you will be able to "view" the data in a particular order without specifying a order by? without a order by you will get the data returned to you on whatever order SQL Server decides to give it to you. and even if you create a clustered index on the order you expect them to be returned this is not a warranty that the records will be retrieved on that order.

you need a particular order when retrieving the records you add a order by - not doing it may give you unexpected results further down the line.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[johnherman]

As Frederico states, there is no guarantee that data will come out in any particular order without an ORDER BY clause. That said, if data is loaded into a new table with no indexes, it tends to come out in the same order that it went in, when a SELECT is run. However, there are no guarantees. And when rows are inserted, updated, or deleted, the initial load order is often no longer in place.

Use the ORDER BY clause.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive innovative agile big data clouds)

 

[DaveInIowa]

Since there's no inherent order to records in a table, there wouldn't be any sense to using ORDER BY in your SELECT either. The only time you would be interested in the order is when you are retrieving the HistData records at a later date. In fact I was surprised SSMS didn't balk when I used the ORDER BY clause in this manner. Even though I used an ORDER BY in the INSERT INTO/SELECT, the record order in the result table didn't match the order they were inserted.

 

[DougP]

Ok this is what I ended up finding out and doing. I added a clustered index having the key field sorted Descending gives the desire results I was looking for.
So when I do insert into [HistData] they show up correctly now
Case closed

CREATE CLUSTERED INDEX [IX_Emini5_HD_Date_HD_Time]
ON [dbo].[Emini5$]] (HD_Date DESC, [HD_Time] DESC)

DougP

 

[gmmastros]

Just so you know....

Creating a clustered index does not guarantee your sort order. It may seem to work now, but there's no guarantee that it will work forever. Seemingly random things can cause problems. For example... 6 months from now, someone may add another index to the table which could cause you problems.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

What the others already said: You have to query ORDER BY [HD_Date] DESC, [HD_Time] DESC anyway, if you want data in that order guarantueed.
Even one more reason against such a clustered index: Surely your normal queries on the history of one ticker would rather profit of another clustering, mainly by ticker.

Just do a normal nonclustered index for your comfort of viewing history ordered AND use the order by. You don't get around it anyway, to get a guaranteed order.

Notice: While the clustered index contains all data. It's only in its leaf nodes, in all its other nodes it's a normal index containing only the fields you put in its definition. The leaf nodes are not ending in records, but in pages or records each, but they don't necessarily contain records in order for sake of reducing write and reorganisation costs. Besides even on the granularity level of the pages a reorganization due to inserts and updates isn't always done up to the point the siblings pages and records of a clustered index leaf node are in sort order for the same reason to reduce write costs. So the physical order of pages is not the index order 1:1. Only an ORDER BY clause makes SQL Server check and apply that order down to row level. An read without any order is just in the mainly, roughly order of the pages and record inside them. The fact new history records always are appended to the end and fill up one current last page makes a bit of a difference, but I'd not rely on that.

Bye, Olaf.

 

[SkipVought]

Who imposed the "requirement" that Order By must not be used? Sounds like one of those requirements that come down from some ignorant manager.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Olaf Doschke]

The question really is, why someone having that requirement doesn't get a frontend doing that for him. Either a full blown data query tool or as minimum a view defined with that ORDER BY you can right click and view in SSMS. Tables are just sets of data, they are not rows of data and they don't have a physical row order as they are composed of pages with rows, and these pages are not organised sequential as in a book, they are rather leafs of a tree. Now tell me how to ensure row order on a lot of sheets you hang on a tree.

Bye, Olaf.

 

[fredericofonseca]

an easy way to get around those dumb people is to get them access to views, not tables - on the view you can put the order by as you see fit

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886