Re: Limitting rows with ROWNUM

Alvin Sylvain wrote:
>
> Some months back I asked about limiting the number of rows returned
> in a query in Oracle SQL.
>
> The answer is to use the ROWNUM pseudo-column.
>
> Eg:
>
> select columnname
> from tablename
> where rownum <= MAXROWS
>
> Someone mentioned a potential problem (which has just been mentioned
> again) regarding the fact that, if you do an "order by", Oracle will
> number the rows before ordering. Therefore, the query will stop at
> a specific quantity of returns, but there will be gaps.
>
> The solution to this problem was also posted, and I forgot to save
> it. We're in such a rush, just being able to limit the rows was
> enough at the time. We're still in a rush, but the issue might
> come up. Better to be ready in advance.
>
> So, if it's not too much of a bother, I'd be massively grateful if
> the solution could be re-posted.
>
> Thanks in advance!
>
> Alvin Sylvain
> alvin_at_c-square.com
> ++ Spams and Junk-mail are NOT appreciated. ++

What you have to do is to create a view or the table 'tablename'.

	CREATE VIEW tablename_view as
		SELECT columnname FROM tablename
		GROUP BY columnname;

and then you use that view for your select :

	SELECT columnname from tablename_view
	WHERE rownum <= MAXROWS;

et voilà ...

I hope this helps.

-- 
CSJ
csjean_at_logimens.com