display first 3 rows

Hi

I was wondering if anyone know how to retrieve first 3 rows from a group by query

something like

select tablespace_name, sum(bytes)
from dba_free_space
group by tablespace_name
order by 1

But only the first 3 rows

SELECT *
FROM
(
select tablespace_name, sum(bytes)
from dba_free_space
group by tablespace_name
order by 1
)
WHERE ROWNUM < 4

- Chris

Mysql has a great function I wish would get added to Oracle called limit. Using that, your query would be


select tablespace_name, sum(bytes)

from dba_free_space

group by tablespace_name

order by 1
limit 3


You can also give ranges, like limit 10 20, start at the 10th record and show 20 more, etc. It's really handy for things like that.

But as far as Oracle now, I think Chris's way is the way to go unless you want to start using pl/sql.

yes I think chris way is the only way with SQL, I knewI could do it in where clausewith rownum but since I wasextracting from a group of results things becamea bit tricky anddidnt think about sub-query!
Thanx

I tried it doesnot work, get error:

SQL> select *
2 from
3 (
4 select tablespace_name, sum(bytes)
5 from dba_free_space
6 group by tablespace_name
7 order by 1
8 )
9 where rownum < 4;
order by 1
*
ERROR at line 7:
ORA-00907: missing right parenthesis

???

It works on my machine: SQLPLUS 8.1.6

I did the test :

On 7.3.4 : no success
On 8.1.6 : success

Regards
Gert

It's because you can not use ORDER BY in a view in versions before 8.1