Platform independent select ordered by primary key 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;
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.