Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Benefits Of Using SQL Ordinal Position Notation?

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?

like image 331
OMG Ponies Avatar asked Feb 12 '10 15:02

OMG Ponies


People also ask

What is ordinal position in SQL?

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.

What is a column ordinal?

the column ordinal – A number that represents the position of the column in a set of columns. So, if a table has 3 columns, named Name, Address, and Zip, in that order, their ordinals are 0, 1, and 2. the column ordinal. – A number that represents the position of the column in a set of columns.


2 Answers

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.

like image 153
gbn Avatar answered Oct 01 '22 10:10

gbn


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.

like image 21
mattmc3 Avatar answered Oct 01 '22 11:10

mattmc3