Benefits Of Using SQL Ordinal Position Notation?

31
votes

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?

Disclosure

This might sound like a homework assignment, but it's really research for an educational lunch the office puts on every month. They pay for lunch, we have to provide a small topic of interest.

6
sqlsql-serveroracleordinals
How often do you change the order of columns? Why would you need to do that? – FrustratedWithFormsDesigner
@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. – OMG Ponies

6 Answers

35
votes

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.

15
votes

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.

6
votes

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.
2
votes

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.

1
votes

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

0
votes

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.