Re: Need some help with ROWNUM

From: Gad Krosner <krosner_at_excite.com>
Date: Tue, 06 Feb 2001 13:50:46 GMT
Message-ID: <95ovfj$1sa$1@nnrp1.deja.com>

That did (mostly) the trick - thanks. It needs a third sort. Also, when going through the last set of results, the 26 needs to be computed such that it shows only the remaining rows for the last page. Here's the solution (not showing computation for last page - done in the app):

SELECT * FROM (
SELECT * FROM (
SELECT * FROM (
	SELECT columns
	FROM tables
	WHERE join conditions, including outer join
	ORDER BY some columns)

WHERE ROWNUM < 1026
ORDER BY some columns DESC)
WHERE ROWNUM < 26)
ORDER BY some columns

In article <sntu7t022qo8dvslpufuis7uv3jsjd5tf8_at_4ax.com>,   Doug C <dcowles_at_i84.net> wrote:
> Rather than running it twice, how about running it once with
> select * from
> (
> select * from
> ((
> SELECT columns
> FROM tables
> WHERE join conditions, including outer join
> ORDER BY some columns
> )
> WHERE ROWNUM < 1026
> )
> ORDER IT BACKWARDS
> )
> WHERE ROWNUM < 26

>

> Not sure if I have all the matching parenthesis etc.,
>
> On Mon, 05 Feb 2001 18:02:52 GMT, Gad Krosner <krosner_at_excite.com>
 wrote:
>
> >Thanks, Steve. I tried that route and it didn't work quite as
 expected.
> >
> >For one thing, it didn't return exactly 25 rows. I got either 21 for
> >rownum in the range 1001 and 1026, or 27 for rownum between 2001 and
> >2026.
> >It also seems to run twice as long because these queries run
> >independently.
> >
> >
> >In article <t7to4v6mlocv5a_at_corp.supernews.co.uk>,
> > "Steve Blomeley" <steveblomeleyATyahoo.co.uk> wrote:
> >> Gad,
> >>
> >> How about ...
> >>
> >> SELECT * FROM
> >> (
> >> SELECT columns
> >> FROM tables
> >> WHERE join conditions, including outer join
> >> ORDER BY some columns
> >> )
> >> WHERE ROWNUM < 1026
> >> #
> >> MINUS
> >> #
> >> SELECT * FROM
> >> (
> >> SELECT columns
> >> FROM tables
> >> WHERE join conditions, including outer join
> >> ORDER BY some columns
> >> )
> >> WHERE ROWNUM < 1001
> >>
> >> hth
> >> Steve Blomeley
> >>
> >>
> >
> >
> >Sent via Deja.com
> >http://www.deja.com/

>
>

Sent via Deja.com
http://www.deja.com/ Received on Tue Feb 06 2001 - 07:50:46 CST