DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: display first 3 rows

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    SELECT *
    FROM
    (
    select tablespace_name, sum(bytes)
    from dba_free_space
    group by tablespace_name
    order by 1
    )
    WHERE ROWNUM < 4

    - Chris

  3. #3
    Join Date
    Jun 2000
    Posts
    417
    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.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  5. #5
    Join Date
    Nov 2000
    Posts
    245

    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

    ???

  6. #6
    Join Date
    Oct 2000
    Posts
    123
    It works on my machine: SQLPLUS 8.1.6

  7. #7
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    I did the test :

    On 7.3.4 : no success
    On 8.1.6 : success

    Regards
    Gert

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  


Click Here to Expand Forum to Full Width