In the following query, I can see that the WHERE clause takes place before the ORDER BY.
SELECT column1, column2, column3 FROM MyTable WHERE rownum < 2 ORDER BY column1
Is there a way I can reverse this? My objective is to first sort the rows and then to pick the first one in the sorted rows.
Before I answer this, please allow me to make a general comment. Our objective is not to first sort the rows and then do something. That's a procedural solution. In other words, that's thinking like an old-fashioned programmer: do this, then do that, then do something else, and this will produce the result. It might work, but often it's not the best solution.
Sorting an entire table means that the database will actually read each row (and perhaps rewrite/copy it several times, depending on the sort algorithm). Sorting a large table is an immensely expensive operation, as computer operations go. Yes, sorting the entire table to see which row ends up in first place does work. But it's not the right approach.
Instead, let's try stating the problem not in terms of how to get what we want, but rather, just what we want. In this case it's simple. We want the row which has the lowest value of column1.
select column1, column2, column3 from MyTable where column1 = ( select min(column1) from MyTable )
Okay, to be fair, this is the "Top 1" problem, which is a special—and simpler—case of the more general "Top N" problem. Interestingly enough, various database vendors have "solved" the more general problem with special but proprietary syntax such as TOP, LIMIT, or ROWNUM. See FIRST N rows, TOP N rows, LAST N rows, BOTTOM N rows... Note, however, that TOP, LIMIT, or ROWNUM all involve sorting the table, so they might not perform very well.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading