Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Unhappy Unanswered: select number of rows

    hi all

    I need to select the max 5 vlaues of field1 from table1

    thanx

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    select field1 from (select field1 from table1 order by field1 desc) where rownum<=5
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by shoblock
    select field1 from (select field1 from table1 order by field1 desc) where rownum<=5
    This will work if you want the top five value, even if they are the same. (5,5,5,4,3,2,1) -> 5,5,5,4,3
    or if you want the top 5 unique values (5,5,5,4,3,2,1) -> 5,4,3,2,1
    then do the following

    select field1 from (select distinct field1 from table1 order by field1 desc) where rownum<=5
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Wow,

    Purely from instinct, I was going to say that SQL looks incredibly inefficient. So I just tried it on a PK'd column and am highly impressed with the result.

    The CBO deduced that we wanted the top five values from an "INDEX FAST FULL SCAN" but I was half expecting it to FULL TABLE SCAN followed by a SORT.

    Got to go away and marvel at the guys who wrote the CBO.

    Cheers
    Bill - still obsessed with performance :-)
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •