Consider the following SQL query:
SELECT title, name FROM Employee ORDER BY title, name DESC;
In output of above query the different titles are listed in _______ order and names of employees having same title are listed in _______ order.

Question

Consider the following SQL query: 
SELECT title, name FROM Employee ORDER BY title, name DESC; 
In output of above query the different titles are listed in _______ order and names of employees having same title are listed in _______ order.

Accepted Answer

The correct option is (2):
The ORDER BY clause in SQL is a fundamental tool used to specify the sequence in which the rows of the result set should appear. When multiple columns are specified, such as in `ORDER BY title, name DESC;`, the sorting is hierarchical: the first column, `title`, dictates the primary sort order. In standard SQL, if the sort direction (ASC for Ascending or DESC for Descending) is omitted for a column, the default behavior is always **ASCENDING** order. Therefore, all the distinct `title` values will be listed from A to Z. For the secondary sort, which is applied only to rows that share the same `title`, the `name` column is explicitly followed by the **DESC** keyword. This modifier overrides the default, ensuring that the names of employees having the same title are sorted in **Descending** order (Z to A).

The incorrect option is (1):
The option "Ascending, Ascending" would only be accurate if the SQL query had been written as `ORDER BY title, name ASC;` or simply `ORDER BY title, name;`. While the `title` column correctly defaults to Ascending order, this option fails because the `name` column is explicitly modified by the `DESC` keyword in the original query. The explicit `DESC` keyword after `name` takes precedence over the default Ascending behavior, thereby making the secondary sort Descending, not Ascending.

The incorrect option is (3):
The option "Descending, Ascending" is incorrect as it misrepresents the sort order for the primary column, `title`. For the `title` column to be sorted in Descending order, the query must explicitly specify it as `ORDER BY title DESC`. Since the `title` column is listed without a direction, it defaults to Ascending. Furthermore, this option incorrectly states that the secondary sort for `name` is Ascending, despite the query explicitly using `DESC` for the `name` column.

The incorrect option is (4):
The option "Descending, Descending" is incorrect because it assumes a Descending sort for the primary column, `title`. As discussed, the default sort order for any column in the ORDER BY clause is Ascending unless the `DESC` keyword is explicitly used. The `DESC` keyword in the provided query only applies to the `name` column that immediately precedes it. Therefore, since `title` has no modifier, it cannot be sorted in Descending order.