22

I had a question in MySQL, did it correctly. But the book code differs a little.

Book:

use tennis;
select playerno, datediff(coalesce(end_date, current_date), 
begin_date) as Difference, position
from committee_members
where datediff(coalesce(end_date, current_date), begin_date) > 500
order by 1;

What is this order by 1 ?

My code also works and is almost the same except:

select playerno, datediff(coalesce(end_date, current_date) AS Data,
order by Data;
Mat
  • 202,337
  • 40
  • 393
  • 406
Master
  • 415
  • 2
  • 4
  • 11

2 Answers2

37

order by 1 means "order by the first field I selected" -- i.e., in this case, the same as order by playerno, because playerno was the first field in the list.

In case you want the official wording, here's what the SQL-92 standard1 says:

10)If ORDER BY is specified, then each <sort specification> in the
        <order by clause> shall identify a column of T.

        Case:

        a) If a <sort specification> contains a <column name>, then T
          shall contain exactly one column with that <column name> and
          the <sort specification> identifies that column.

        b) If a <sort specification> contains an <unsigned integer>,
          then the <unsigned integer> shall be greater than 0 and not
          greater than the degree of T. The <sort specification> iden-
          tifies the column of T with the ordinal position specified by
          the <unsigned integer>.

In this case, b is the one that seems to apply.

More recent versions of the SQL standard have removed this capability though, so new code should generally avoid it. SQL-based database servers have been deprecating it for a while now, but most continue to support it for the sake of backward compatibility. At the same time, the fact that they've deprecated it indicates they no longer consider it a feature they really need to support, so it could be removed at any time with no further warning (e.g., if they find a bug in that part of their code, they might decide the best way to fix the bug is to just disable that feature).

1. This quote is from a freely-available draft rather than the approved standard. While I'm sure there are at least a few changes between this draft and the final text of the standard (not to mention between one version of the standard and another) it seems unlikely that something this fundamental would change between the draft and the final standard.
Jerry Coffin
  • 476,176
  • 80
  • 629
  • 1,111
  • 2
    Rather than a compliance standard, let's back it up with MySQL's own docs: https://dev.mysql.com/doc/refman/5.7/en/select.html keyword: 'order by 2' – Sinkeat Aug 10 '17 at 08:03
  • 1
    it's worthwhile to highlight this statement from the MySQL docs above. `Use of column positions is deprecated because the syntax has been removed from the SQL standard.` – asgs Sep 08 '21 at 09:37
7

This is known as "ORDER BY ordinal", basically order by the column in that position. Order by 1 means order by the first selected column. In your example, it would be the equivalent of saying ORDER BY playerno

I wouldn't recommend doing it this way though as it's not clear what column it's referencing and if the column order changes the query will return different results.

More resources:

Quick Tip: Order By 1 Desc

Bad habits to kick : ORDER BY ordinal

SQL: order by

Community
  • 1
  • 1
sachleen
  • 30,730
  • 8
  • 78
  • 73
  • Working link: [Quick Tip: Order By 1 Desc](https://web.archive.org/web/20150311084949/http://mattberseth.com/blog/2007/05/quick_tip_order_by_1_descendin.html) – chemturion Apr 30 '20 at 18:08