CODEWITHSUNDEEP
sortingoracle11g
Sanjay
Sanjay

Reputation: 1108

How "ORDER BY" able to sort when column is not part of retrieved data?

I have a table lets say TEST_ORDERBY in which I have two column NAME and AGE. as for my understanding ORDER BY clause used to sort the rows retrieved in a SELECT statement.

I used below query :

SELECT NAME FROM TEST_ORDERBY ORDER BY AGE;

In this query I have selected NAME and used ORDER BY on age, and result is properly sorted based on AGE. I am not able to understand

how ORDER BY sort the result on the basis of that column which is not selected or which is not part of retrieved data?

I have done some google and found that ORDER BY can sort by columns in the table, regardless of whether the columns appear in the SELECT statement’s select list or not but HOW is not mention.

Upvotes: 3

Views: 1378

Answers (3)

Mark Zellers
Mark Zellers

Reputation: 1

The database needs to generate not only the values in the SELECT clause, but also any values needed by the WHERE, GROUP BY, HAVING and ORDER BY clauses. Some of those values might not even exist in the original table(s).

Note that in some cases, the database can order the result without doing an explicit sort (for example, when the order requested matches the primary key or some other index, the db can scan the index in ascending order and return the result which will not need to be sorted).

More commonly, once the data has been ordered, there is an extra filtering step that removes any columns that are not requested by the SELECT clause.

Upvotes: 0

Spencer Wieczorek
Spencer Wieczorek

Reputation: 21575

This is because ORDER BY happens before SELECT. In other words the entire table is retrieved, then ordered, then restricted to just the NAME column. So when the ordering happens we did not restrict to certain columns with SELECT ....

Upvotes: 3

Barmar
Barmar

Reputation: 781048

Each row that is retrieved comes from a row in the table. It's sorting by the column in the row associated with the selected columns.

Upvotes: 0

Related Questions