Forum OpenACS Q&A: Porting Oracle's ROWNUM

Posted by Roberto Mello on 08/31/00 05:29 AM

Is there a PG function that does the equivalent of Oracle's ROWNUM ?
This is what Oracle's docs say about ROWNUM:

"For each row returned by a query, the ROWNUM pseudocolumn returns a
number indicating the order in which Oracle selects the row from a
table or set of joined rows. The first row selected has a ROWNUM of 1,
the second has 2, and so on."

Thanks in advance...

2: Response to Porting Oracle's ROWNUM (response to 1)

Posted by Don Baccus on 08/31/00 02:32 PM

What do you want to use it for? In web/db, most folks talk about using it to select n rows starting with an offset, which can be done easily in postgres using "limit" and "offset:

select * from foo limit 10 offset 30;

returns 10 rows starting with the 30th.

There's no PG function or variable that corresponds with ROWNUM, so if you're trying to do something other than the above, describe the problem and I'll try to help out.

3: Response to Porting Oracle's ROWNUM (response to 1)

Posted by Roberto Mello on 08/31/00 04:13 PM

That's exactly what I am trying to do... Thanks Don.

However, this brought me to another question. This is the query I am working on:

     select * from (
        select log_id, job_id, to_char(stamp, 'MM-DD HH:MI:SS') as stamp, log_msg
        from cs_processing_log
        where $job_id_condition
        order by log_id desc
     )
     where rownum <= 10
     order by log_id

In my tests PG does not seem to accept the outer select * from (). Am I correct?

In this particular case, would it seem plausible for me to rewrite this query as:

        select log_id, job_id, to_char(stamp, 'MM-DD HH:MI:SS') as stamp, log_msg
        from cs_processing_log
        where $job_id_condition
        order by log_id desc
	limit 10

Thanks.

4: Response to Porting Oracle's ROWNUM (response to 1)

Posted by Dan Wickstrom on 08/31/00 08:08 PM

pg doesn't support subselects in from clauses, and it probably won't support it until they do the query-tree rewrite which is slated to happen for pg version 7.2.

Your modified query looks fine.

5: Response to Porting Oracle's ROWNUM (response to 1)

Posted by Michael A. Cleverly on 08/31/00 08:13 PM

And even on databases that support subselects in from statements (like Solid! 😊 it's illegal (and wouldn't make any sense if it weren't) to have an order by in the subselect.