-
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
<font face="courier">
select tablespace_name, sum(bytes)
from dba_free_space
group by tablespace_name
order by 1
limit 3
</font>
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
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|