Matt Good posted (a couple of weeks ago) about using ROW_NUMBER()...
> So, a query like this:
> SELECT id, name FROM my_table ORDER BY id LIMIT 10 OFFSET 20
>
> Would become:
> SELECT id, name FROM
> (SELECT id, name, ROW_NUMBER() OVER (ORDER BY id) AS ora_row_num
> FROM my_table ORDER BY id)
> WHERE ora_row_num > 10 AND ora_row_num <= 20
>
> This eliminates one level of subselects from the approach I previously
> posted. The "OVER (ORDER BY..." is required for ROW_NUMBER, so if the
> query didn't specify an ordering, then a default such as the primary key
> would need to be added.
Just to flag that unless you need to display the returned results in a
different order to that used for the limit/offset counting, I'm fairly
sure that the second " ORDER BY ID" isn't needed,
i.e.
SELECT id, name FROM
(SELECT id, name, ROW_NUMBER() OVER (ORDER BY id) AS ora_row_num
FROM my_table)
WHERE ora_row_num > 10 AND ora_row_num <= 20
will do fine...
/Gwyn
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
