Benefits Of Using SQL Ordinal Position Notation?

Question

Background Information

Ordinal position notation, AKA ordinals, is column shorthand based on the column order in the list of columns in the SELECT clause, instead of either the column name or column alias. Commonly supported in the ORDER BY clause, some databases (MySQL 3.23+, PostgreSQL 8.0+) support the syntax for the GROUP BY clause as well.

Here's an example of using Ordinals:

GROUP BY 1, 2
ORDER BY 1, 2

It's not good to use because it makes the query brittle - if the column order changes, the ordinals need to be updated or your query won't return what you thought it would. Very likely, you'd get an error when used in the GROUP BY if the columns at those locations are wrapped within aggregates...

The Question

The only benefit I can think of is less data to send over the wire, if you aren't using stored procedures or functions (which make ordinal usage moot, to me anyways). Are there any other benefits I'm missing?

Villager

How often do you change the order of columns? Why would you need to do that? – Appalachian

@FrustratedWithFormsDesigner: At the behest of clients, anything can and will change. Ordinals are a maintenance concern - if you give the job to someone unfamiliar with SQL in general, they'd likely miss the notation and then the fun begins. To me, performance is a marginally higher priority than maintenance because it's possible it won't be me making updates. – Villager

Answer 1

I'd use it:

• If you love troubleshooting
• Creating adhoc queries without intellisense

There is no upside.

SQL Server only supports in the ORDER BY anyway. Anywhere else it's an expression to be evaluated.

Bodgie

Hpw about when the group by statements are in the dozens of lines length. How fun is that to code/maintain in two places? This is not hypothetical: I have needed to do that in Spark and Hive Sql .Yes - that is the ansi standard (since projections happen after group by). But the rationale for the ordinal should now be clear. – Gnathonic

Answer 2

Often times when I'm querying a table with a lot of columns (in ad-hoc-land just for data exploration... I would never code like this for a PROD environment) I do something like this to get fields I care about close together:

select top 1000
  Col_1, Col_18, Col_50, Col_117, *
from
  TableWithTonsOfCols
order by
  1, 4 desc, 3

If I said order by Col_1, Col_117 desc, Col_50 my query would barf because the statement wouldn't know which columns I meant to order by due to the " * " doubling up. Not very common, but still a useful feature.

Swimmingly

FYI: SQL Server 2005 introduced this "barfing" to remove ambiguity in the ORDER BY. – Bodgie

Answer 3

The two use cases for me are:

• I am in a hurry and don't want to type, so I use the ordinal. I would always convert this to the column name for any non-temporary use
• the column I am ordering by is a lengthy CASE statement; rather than retyping the CASE statement for the ORDER BY clause, I use the ordinal which keeps it DRY. There are ways around this, e.g., using CTEs, subqueries, or view, but I often find the ordinal is the simplest solution.

Phillie

Like I mentioned in my comment to Randy's answer, you can use the column alias in the ORDER BY. – Villager

Answer 4

I tend to use in-line views now:

select col_a, count(*) from
  (select case ...... end col_a from ...)
group by col_a
order by col_a;

But in the days before they were valid syntax, it did help retyping the full text of the column. With tricky functions you had the potential for discrepancies between the value in the SELECT and ORDER BY such as

select ltrim(col_name,'0123456789')
from table
order by ltrim(col_name,'123456789')

The '0' in the SELECT means that you are not ordering by what you select.

Brewington

Answer 5

I have a query generating algorithm - the SQL is auto generated. Using the ordinal means that I can refer to the generated field without having to fetch the field name again. The user can refer to the field name in a table by selecting it from a list on the screen. As long as I make the list correspond with the sql, I would never need to know field names, if the SELECT items were ordinal, too.

Memory says this used to be in the SQL standard in the late 1970's

Shluh

Answer 6

If I recall correctly, the use of ordinals like you describe is being deprecated by Microsoft in a future release of SQL Server. I could be wrong on this, but I think that's the case. I've always liked using them in certain cases because it involves less typing when you're dealing with derived columns that contain a longish query.

Papuan

God, I hope so. I haven't come across any such documentation, would love a link if you happen to have it. For a computed column, I'd refer to a column alias in the ORDER BY - generally can't be helped for the GROUP BY though... – Villager

It's recommended against, but not deprecated: msdn.microsoft.com/en-us/library/ms188385.aspx – Calvo