Forum OpenACS Q&A: Porting Oracle's ROWNUM

Collapse
Posted by Roberto Mello on
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...

Collapse
Posted by Don Baccus on
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.

Collapse
Posted by Roberto Mello on
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.
Collapse
Posted by Dan Wickstrom on
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.

Collapse
Posted by Michael A. Cleverly on
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.