Sequence of WHERE and ORDER BY clauses
In this query, I can see that the WHERE clause takes place before the ORDER BY. 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.
In the following query, I can see that the WHERE clause takes place before the ORDER BY.
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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.
