rownum

15 Things You Should Know about the ORDER BY Clause

  1. When multiple columns/expressions are specified in the ORDER BY clause, the precedence of sorting is left to right.

  2. The ORDER BY clause can order in ascending (ASC) or descending (DESC) sequence, or a mix of both. If ASC or DESC is not explicitly stated, then ASC is the default.

  3. ORDER BY ASC places NULL values at the end of the query results. ORDER BY DESC places null values at the start of the query results.

    4. [click to continue…]

{ 5 comments }

The Special DUAL Table in Oracle

DUAL is a special one-row, one-column table in Oracle data dictionary.

Of what use is DUAL table in Oracle?

DUAL comes in handy when you want to select just one row through a query. Oracle SQL structure requires you to have a FROM <table> clause, but some queries don’t need a table — if you want to know the current system date, for example, or the answer for (3+1)*5. DUAL is useful for queries you’d write for such cases:

[click to continue…]

{ 0 comments }

The Magic Of ROWNUM

The “ROWNUM greater than” query never fails to have an eye-popping effect  the first time anyone sees it. If you haven’t worked with ROWNUM in Oracle much before, be prepared!

First things first. What is ROWNUM?

ROWNUM is a pseudocolumn, assigning a number to every row returned by a query. The numbers follow the sequence 1, 2, 3…N, where N is the total number of rows in the selected set. This is useful when you want to do some filtering based on the number of rows, such as:

[click to continue…]

{ 3 comments }

Selecting ODD or EVEN rows from a table

Question: How can I select only the even/odd rows from an Oracle table?

Answer: Talking of “even or odd rows” is meaningless in Oracle until you have ordered the rows. Oracle does not store rows in a specific order – the order has to come from the query.

Once the order is specified, then the query to retrieve odd rows or even rows can be written in this form:

[click to continue…]

{ 8 comments }