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;