Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a reasonably standard way to perform a select on a single table and have the result set ordered by primary key without having to specify the primary key condition explicitly?

Ideally I wish I could perform a query such as the following, invalid one:

select * from my_table order as primary key;
share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

The SQL Standard requires that you specify an explicit column list if you require the result set to be ordered.

Many RDBMS return the results of a single table select in clustered index order. As the primary key again is enforced by the clustered index by default, this gets you close. however, there is no guarantee of this order. Things that could cause an engine to return the rows in a different order are for example parallelism within the query or other concurrent interactions with that table.

There is a fairly standard way to construct a dynamic select that has your desired order by clause specified. You can use the INFORMATION_SCHEMA management views for that purpose. However this approach falls short when it comes to actually execute the dynamic SQL as there are significant differences between the different vendors.

So in short, the answer is: NO.


There is one, admittedly small exception where you can get away without knowing the column name of a single column surrogate primary key in SQL Server:

CREATE TABLE dbo.A(i INT IDENTITY(1,1) PRIMARY KEY CLUSTERED);
GO

SELECT * FROM dbo.A ORDER BY $IDENTITY;

The key word $IDENTITY always references the one identity column of the table. But this is SQL Server specific.

share|improve this answer
2  
Just a note, in SQL Server 2012, IDENTITYCOL is deprecated, use $identity instead. –  Max Vernon Nov 22 '13 at 16:14
add comment

"Another fundamental aspect of a set is that there’s no relevance to the order of the elements. For this reason, rows in a table have no particular order, conceptually. So when you issue a query against a table and don’t indicate explicitly that you want to return the rows in particular presentation order, the result is supposed to be relational. Therefore, you shouldn’t assume any specific order to the rows in the result, never mind what you know about the physical representation of the data, for example, when the data is indexed."

querying Microsoft sql server 2012 70-461 training kit.

share|improve this answer
    
On the other hand primary keys and indices express ordering relationships on the records in a table so it would seem reasonable to wish to be able to extract records in that order without having to specify it again. –  Nicola Musatti Nov 22 '13 at 15:10
    
It might seem reasonable, however to rely upon the functionality of what is for all intents-and-purposes a black box is asking for trouble. Specifying an ORDER BY clause is the ONLY way to guarantee a specific order. –  Max Vernon Nov 22 '13 at 16:10
2  
I fail to see how this answers the question. The OP seems to know that an ORDER BY is needed to have a result set returned in a specific order. –  ypercube Nov 22 '13 at 17:19
add comment

There is no such feature and that's a good thing in my opinion.

A PRIMARY KEY constraint does not define or imply an ordering of the table. There is no such thing as a default order in SQL and the key might even be made up of attributes for which ordering operations do not apply (e.g. binary, boolean or geographic types). In relational model terms a "primary" key is no different in form or function from other keys of the same table and it is for the user to decide at query time, not design time, what keys to use and how. It is not really helpful to tie special functions only to one key and not another. Some DBMS products regrettably already do tie certain functions to the PRIMARY KEY constraint. That's a limitation though - it's not a "feature".

In principle keys cannot and do not imply order. Keys are made up of attributes but the types of those attributes are not necessarily ordered types. Keys only require that values of a type are comparable to each other, i.e. = and != must be valid operations for every type but > and < need not be. Some fairly common types do not support ordering.

A second reason why keys don't imply ordering is that keys are sets of attributes which are themselves unordered. So given a key of {a,b,c} even if a, b, c are all attributes which are capable of sorting there are actually six different ways the table could be sorted (a|b|c, b|c|a, c|b|a, ... etc). Possibly the syntax used to define a key might also be used to specify an order of the key attributes but any syntax that relied on such attribute ordering ought to be considered a defect in a supposedly relational DBMS. In the relational model attributes are not ordered.

share|improve this answer
    
I'm not convinced. Although it may well be considered an implementation detail, a unique key does imply an ordering on a table's rows. I'm not asking for a default ordering, as that would defy the set theory foundation of relational databases. On the other hand, as ways to retrieve data in a given order are available in the order by clause I don't see why one should have to restate an ordering criterium which was already specified. –  Nicola Musatti Nov 25 '13 at 20:23
    
Note that my fake order as syntax could be easily be extended to allow order as index <index name> –  Nicola Musatti Nov 25 '13 at 20:24
    
@Nicola one of sqlvogel's point is that some indexes (like spatial indexes or indexes in a bit column is some dbms) do not imply an ordering, so order as index <index name> would be void in those cases. –  ypercube Nov 25 '13 at 21:05
    
In many DBMS, there are also indexes implemented not using B-trees, like Postgres which has hash and gist indexes. Ordering by a hash index is rather ill-defined. –  ypercube Nov 25 '13 at 21:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.