Why does using "where rownum = 1" not select the first ordered row?

Question

This is very weird, so I could use a pair of spare eyes to understand what's happening.

So I have this query:

WITH x as (
            SELECT num_aula, tipo_aula, min(abs(capienza-1)) score
            FROM aula
            JOIN (
                    select num_aula, tipo_aula
                    from aula
                    where tipo_aula = 'Laboratorio'
                    minus
                    select num_aula, tipo_aula
                    from occr_lezione
                    where to_char(Data_inizio_occr_lezione,'hh24:mi') = '12:30'
                    and Nome_sede = 'Centro Direzionale'
                    and Giorno_lezione = 2
                 )
            USING(num_aula,tipo_aula)
            GROUP BY num_aula, tipo_aula
            ORDER BY score asc
)
SELECT *
FROM x

which return this result set:

NUM TIPO_AULA                 SCORE
--- -------------------- ----------
1   Laboratorio                  35
2   Laboratorio                  35

Which is the desired result.

Now, if I add this line to the query:

WHERE rownum = 1;

which should return the first row of the table, I get this:

NUM TIPO_AULA                 SCORE
--- -------------------- ----------
2   Laboratorio                  35

How is that possible?

Answer 1

I think the query you really want is

WITH x as (
    SELECT num_aula, 
           tipo_aula, min(abs(capienza-1)) score, 
           row_number() over(partition by num_aula, tipo_aula order by score asc ) as seq
    FROM aula
    JOIN (
        select num_aula, tipo_aula
          from aula
          where tipo_aula = 'Laboratorio'
          minus
          select num_aula, tipo_aula
            from occr_lezione
            where to_char(Data_inizio_occr_lezione,'hh24:mi') = '12:30'
            and Nome_sede = 'Centro Direzionale'
            and Giorno_lezione = 2
        )
        USING(num_aula,tipo_aula)
)
SELECT *
FROM x
WHERE x.seq = 1;

The ROWNUM keyword does not behave as you think, see this article about rownum.

To give more details, the ROWNUM are assigned before any order is given to the result set.

If you really want to get the correct result using the ROWNUM keyword, then you could achieve this with a subquery that would first order, then generate the rownum to the actual ordered result set. However, I would prefer the first approach as it is more readable in my opinion, but you are free to prefer this one.

SELECT *
FROM (SELECT num_aula, 
           tipo_aula, min(abs(capienza-1)) score
        FROM aula
        JOIN (
            select num_aula, tipo_aula
            from aula
            where tipo_aula = 'Laboratorio'
            minus
                select num_aula, tipo_aula
                from occr_lezione
                where to_char(Data_inizio_occr_lezione,'hh24:mi') = '12:30'
                  and Nome_sede = 'Centro Direzionale'
                  and Giorno_lezione = 2
            ) USING(num_aula,tipo_aula)
        GROUP BY num_aula, tipo_aula
        ORDER BY score asc) x
WHERE x.rownum = 1;

Answer 2

The rownum is assigned to the row before any sorting takes place, so I suspect that removing your order by and running without the where rownum = 1 will return:

NUM TIPO_AULA                 SCORE
--- -------------------- ----------
2   Laboratorio                  35
1   Laboratorio                  35

There is a good article on rownum that can be found here: http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

Answer 3

I'm not sure the problem here is with the rownum. You are using order by score, but this does not uniquely define the order, because there are duplicate values of score.

Try using order by score, num in order to get a stable sort and see if that fixes your problem.

Note that the lack of a stable sort also affects row_number(), so just switching to row_number() may not fix the problem -- unless you include an addition field to make the sort stable.

Answer 4

SELECT num_aula, tipo_aula, min(abs(capienza-1)) score
FROM aula
JOIN (
        select num_aula, tipo_aula
        from aula
        where tipo_aula = 'Laboratorio'
        minus
        select num_aula, tipo_aula
        from occr_lezione
        where to_char(Data_inizio_occr_lezione,'hh24:mi') = '12:30'
        and Nome_sede = 'Centro Direzionale'
        and Giorno_lezione = 2
     )
USING(num_aula,tipo_aula)
GROUP BY num_aula, tipo_aula
ORDER BY score asc
FETCH FIRST ROW ONLY;

Using FETCH FIRST ROW ONLY will order the results and then return the first row, this way you do not need to select again and have WHERE ROWNUM = 1 .

Answer 5

If you say something like this:

with t as
(
   select *
   from foo
   order by foo.x
)
select *
from t
where t.rownum = 1

1. You haven't specified an ordering via order by in your actual query. SQL is free to return the results set in any order (and even to vary the ordering from one execution to another).

2. The order by in your inner 'select' statement is going to be ignored by the optimizer, unless you're doing something in that inner select with row numbering or partitioning or the like.

3. You're making an assumption that the sort order is stable with respect to the ordering of duplicate values.

So...you need to write something like this:

with t as
(
   select *
   from foo
)
select *
from t
order by foo.x, foo.id
where t.rownum = 1