Oracle provides the ROWNUM pseudo-column to allow someone limit up the number of returned rows in a SELECT statement. However, you should be cautious when using ROWNUM in combination with an ORDER BY clause.

For example, consider the following table:

ColumnName |

30 |

40 |

50 |

60 |

70 |

80 |

90 |

We can easily select the first three (unordered) rows, like this:

SELECT * FROM TableName WHERE ROWNUM <= 3;

ColumnName |

30 |

40 |

50 |

Or we can even select the entire data set in descending order, like this:

SELECT * FROM TableName ORDER BY ColumnName DESC;

ColumnName |

90 |

80 |

70 |

60 |

50 |

40 |

30 |

But, how about selecting the three highest values? A common pitfall is trying something like this one:

SELECT * FROM TableName WHERE ROWNUM <= 3 ORDER BY ColumnName DESC;

But this will falsely and not surprisingly return this result this:

ColumnName |

50 |

40 |

30 |

What did go wrong? The answer is simple. At first, Oracle select the first three rows from the table in their natural order of occurance. They happen to be 30, 40 and 50. Then, Oracle sorts up the result set and returns it.

Intuitively, someone would think that the sorting and the row limit should occur in the reverse order, and that’s correct. This is the proper way to do it:

SELECT * FROM (SELECT * FROM TableName ORDER BY ColumnName) WHERE ROWNUM <= 3;

ColumnName |

90 |

80 |

70 |

In general, when you have an ordered result set, and you want to select only the first N rows of it, this is the syntax you should use:

SELECT * FROM (SELECT * FROM … ORDER BY …) WHERE ROWNUM <= N;