All About ORDINAL_POSITION

Oct 23, 2020 by Robert Gravelle

In relational databases, including MySQL, SQL Server, Oracle, and others, the ORDINAL_POSITION refers to a column’s location in terms of ordering within a table or query output. In today’s blog, we’ll learn how to use ordinal positioning to present columns in our preferred order, using Navicat Premium as our database client.

When viewed in a grid format, columns are ordered from left to right. For instance, here are the columns of an order details table as they appear in Navicat’s Grid View:

Meanwhile, in the Table Designer, you can see that the left to right column order above corresponds to the top -> down order there:

By default, columns occupy the same ordering in which they were created. Column ordering is important because it determines their order for every single table, view, and SELECT queries that are in the database. As we’ll see a little later on, if we don’t like the order of columns in a table, we aren’t stuck with it.

Since you can create views that can obscure the true ordinal positioning of a table, relational databases offer a means of finding out what it is. ORDINAL_POSITION is a column in the INFORMATION_SCHEMA.COLUMNS table. As such, you can find the ordinal position of columns in a table by querying it as follows:

So what do you do if you’d like columns to appear in a different order than they were created? AS I alluded to earlier, you don’t have to keep a column’s original ORDINAL_POSITION. You can change it using one of the following statements:

ALTER TABLE orderdetails MODIFY COLUMN orderLineNumber smallint(6) AFTER quantityOrdered; 

OR:

ALTER TABLE orderdetails CHANGE COLUMN orderLineNumber smallint(6) AFTER quantityOrdered; 

The above statements move the orderLineNumber column from last position to second-last.

In Navicat, the Table Designer has Move Up and Move Down buttons that make changing column ordering a snap:

After selecting the orderLineNumber column, every click of the Move Up button changes its ORDINAL_POSITION by one place:

After saving the table design, the orderLineNumber is now before the priceEach:

Ordinal Positioning is not just about default column ordering, it can also be referenced in SELECT queries as a short-cut for column names. To illustrate, here’s a query that references a couple of columns in the ORDER BY clause:

Rather than spell out each column name in the ORDER BY clause, we can simply refer to the column’s ORDINAL_POSITION within the table:

Shorter SQL, same results!

In today’s blog, we learned how the ORDINAL_POSITION affects a column’s location in terms of ordering within a table or query output, as well as how to use ordinal positioning to present columns in our preferred order.

Interested in Navicat Premium? You can try it for 14 days completely free of charge for evaluation purposes!