Why doesn't my simple query using the table's primary index?

[GoldFoxtrot]

I have a simple query 'select * from fact where ...'. The fact table has a primary index and loaded with several batch of data files in appending way. When I run the query, the result is not ordered by the index. Why? I added ORDER BY in the query and all is fine. But I do like to know how does this happen internally. Thanks!

Dave

 

[PHV]

A query result is ordered only if the select instruction contains an order by clause, dot.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GoldFoxtrot]

Thanks! PH.

I guess this is true in all SQL based databases.

Dave

 

[PHV]

It is true for all relational databases.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

Actually, a sort order can be saved with the query design in Access (in the Order By property) and that overrides the "Order By" clause in the SQL, at least in Access 97.

Also, when selecting from a single table without an ORDER BY clause, Access uses the primary key to sort the records or a unique index if there isn't a primary key but a WHERE clause removes the default sorting as you saw.


John

http://www.interautomateconsulting.com

 

[BNPMike]

JonFer

I hadn't noticed that before. Without a primary key the data comes out in physical order (or at least order loaded). Add a primary key and it comes out in primary key order. Add any old meaningless WHERE clause and it reverts to physical order again.

 

[GoldFoxtrot]

JonFer said:
"when selecting from a single table without an ORDER BY clause, Access uses the primary key to sort the records ...but a WHERE clause removes the default sorting as you saw."

This is exactly what happened. I validated JonFer's point by removing 'ORDER BY' and 'WHERE', the query sorted by the single table's primary index. Add 'WHERE' and the query result becomes unsorted. Since I do need 'WHERE', so I have to add 'ORDER BY' also.

PHV's answer: "A query result is ordered only if the select instruction contains an order by clause, dot." PH was based on my original question that my query must have 'WHERE'. There is no conflict and PH is correct too.

So is BNPMike.

Yes, through our discussion, I totally understand the logic and thanks to you all.

Dave