Skip site navigation (1) Skip section navigation (2)

Re: Re: "Oracle's ROWNUM"

From: Barry Lind <barry(at)xythos(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Re: "Oracle's ROWNUM"
Date: 2001-07-30 16:50:28
Message-ID: 3B659054.2080007@xythos.com (view raw or flat)
Thread:
Lists: pgsql-general
 > If I do
 > SELECT rownum, * FROM foo WHERE rownum > 10 and rownum < 20;
 > will the output rows be numbered 1 to 9, or 11 to 19?

Such a select will never return any rows.  Essentially rownum in a where 
predicate can only be used in the forms: rownum = 1; rownum < n; rownum 
<= n.
Anything else will return no rows.  For example rownum = 2 will return 
no rows because the first row returned by the query has by definition a 
rownum of 1, but the where predicate prevents this row from being 
returned, thus it can never get to a rownum value of 2 to satisfy the 
where predicate.

In Oracle 8.1 they began allowing order by in the from clause to support 
the top-n type selects.  Thus begining in 8.1 a query of the form 
"select  * from (select foo from bar order by foo) where rownum < 10"
  became possible.  Before 8.1 it wasn't legal to have an order by in 
this position, meaning you couldn't get a top-n result that was ordered.

thanks,
--Barry

Tom Lane wrote:

> Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp> writes:
> 
>>Oracle doc says.
>>
> 
>>If you embed the ORDER BY clause in a subquery and place the ROWNUM
>>condition in the top-level query, you can force the ROWNUM condition
>>to be applied after the ordering of the rows. For example, the
>>following query returns the 10 smallest employee numbers. This 
>>is sometimes referred to as a "top-N query": 
>>
> 
>>SELECT * FROM
>>   (SELECT empno FROM emp ORDER BY empno)
>>   WHERE ROWNUM < 11;
>>
> 
> This thing gets more poorly-defined every time I hear about it!?
> 
> Based on what's been said so far, ROWNUM in a WHERE clause means
> something completely different from ROWNUM in the SELECT target list:
> it seems they mean input row count vs output row count, respectively.
> If I do
> 	SELECT rownum, * FROM foo WHERE rownum > 10 and rownum < 20;
> will the output rows be numbered 1 to 9, or 11 to 19?  If I add a
> condition, say "AND field1 < 100", to the WHERE clause, does the rownum
> count include the rows rejected by the additional clause, or not?
> And how do you justify any of these behaviors in a coherent fashion?
> 
> Dare I ask how it behaves in the presence of GROUP BY, HAVING,
> aggregates, DISTINCT, UNION, ... ?
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 
> 



In response to

Responses

pgsql-general by date

Next:From: Stephan SzaboDate: 2001-07-30 16:51:46
Subject: Re: referential integrity violation
Previous:From: Mike FinnDate: 2001-07-30 16:27:24
Subject: Unexpected *ABORT STATE*

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group