CODEWITHSUNDEEP
mysql
ahmad soliman
ahmad soliman

Reputation: 141

Ordering by 'field index'

Can i use order by in mysql query but not with columns name but column index or type order by 'first column' or something like that ?

Like

Select * from `users` order by 1

Upvotes: 0

Views: 38

Answers (3)

Gumbo
Gumbo

Reputation: 655239

Yes, MySQL supports referring to columns in ORDER BY by their position:

Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions are integers and begin with 1 […]

This is also reflected by the syntax description of ORDER BY:

[ORDER BY {col_name | expr | position}
  [ASC | DESC], ...]

However:

Use of column positions is deprecated because the syntax has been removed from the SQL standard.

Upvotes: 1

ray
ray

Reputation: 4267

yes, it's possible, see mysql reference

[ORDER BY {col_name | expr | position}

mysql> select * from kcreate order by 1;
+-------+-------+
| k1    | k2    |
+-------+-------+
|     1 | 99999 |
| 12117 | 10294 |
+-------+-------+
2 rows in set (0.00 sec)

mysql> select * from kcreate order by 2;
+-------+-------+
| k1    | k2    |
+-------+-------+
| 12117 | 10294 |
|     1 | 99999 |
+-------+-------+
2 rows in set (0.00 sec)

Upvotes: 0

Sabuj Hassan
Sabuj Hassan

Reputation: 39365

Yes you can. For example if you use 1000 instead of 1, you'll get something like this error:

ERROR 1054 (42S22): Unknown column '1000' in 'order clause'

Upvotes: 0

Related Questions