Sorting By Column Number (instead of name)

An integer that identifies the number of the column in the SelectItems in the underlying query of the SELECT statement. Column number must be greater than 0 and not greater than the number of columns in the result table. In other words, if we want to order by a column, that column must be specified in the SELECT list.

The rule checks for ORDER BY clauses that reference select list columns using the column number instead of the column name. The column numbers in the ORDER BY clause impair the readability of the SQL statement. Further, changing the order of columns in the SELECT list has no impact on the ORDER BY when the columns are referred to by names instead of numbers. 

Syntax

The Syntax to use ORDER BY Clause with Column Number

ORDER BY Column_Number asc/desc

Sorting By Column Number Example

Here we take an example to sort a database table according to column 1 i.e Roll Number. For this a query will be:

Query:

CREATE TABLE studentinfo
( Roll_no INT,
NAME VARCHAR(25),
Address VARCHAR(20),
CONTACTNO BIGINT NOT NULL,
Age INT ); 

INSERT INTO studentinfo
VALUES (7,'ROHIT','GHAZIABAD',9193458625,18),
(4,'DEEP','RAMNAGAR',9193458546,18),
(1,'HARSH','DELHI',9193342625,18),
(8,'NIRAJ','ALIPUR',9193678625,19),
(5,'SAPTARHI','KOLKATA',9193789625,19),
(2,'PRATIK','BIHAR',9193457825,19),
(6,'DHANRAJ','BARABAJAR',9193358625,20),
(3,'RIYANKA','SILIGURI',9193218625,20);

SELECT Roll_no, Name, Address
FROM studentinfo
ORDER BY 1

Output:

Explanation:

ORDER BY 1 means sorting values according to first column in the SELECT statement.

SQL ORDER BY clause sorts the result of the SELECT statement either in ascending or descending order.

In this article, we’ll explore the ORDER BY clause, exploring its syntax, functionality, and usage with detailed examples.