1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

select top percent in oracle

Discussion in 'SQL PL/SQL' started by monkey, Jun 3, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    monkey likes this.
  3. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    We wrote rank() with empty braces.
    How do we know what it will rank?
    many thanks!!!

    Code (SQL):

      3  (SELECT employee_id, first_name, last_name, salary, rank() OVER (ORDER BY salary DESC) rk
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  5. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    many many thanks, much more clear now, but let's see the table:

    id_footbalplayer mass
    1 80
    2 70
    3 90
    4 60
    5 70
    6 70
    7 90

    No, , I woul dunderstand how to rank the masses, but how do we count right upper 50%, I mean, since masses are repeating...we can actually find upper 50% rows, not upper 50% values...how would we write upper 50% results for beginning, anyway?
    many thanks!!!
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Here is one way:


    Code (SQL):

    SQL> CREATE TABLE fb(
      2          id_footballplayer NUMBER,
      3          mass    NUMBER);


    TABLE created.


    SQL>
    SQL> INSERT ALL
      2  INTO fb
      3  VALUES(1,80)
      4  INTO fb
      5  VALUES(2,70)
      6  INTO fb
      7  VALUES(3,90)
      8  INTO fb
      9  VALUES(4,60)
     10  INTO fb
     11  VALUES(5,70)
     12  INTO fb
     13  VALUES(6,70)
     14  INTO fb
     15  VALUES(7,90)
     16  SELECT * FROM dual;


    7 ROWS created.


    SQL>
    SQL> commit;


    Commit complete.

    SQL> COLUMN rwct new_value rw_ct noprint
    SQL>
    SQL> SELECT COUNT(*) rwct
      2  FROM fb;



    SQL>
    SQL> SELECT r.id_footballplayer, r.mass
      2  FROM
      3  (SELECT id_footballplayer, mass, rank() OVER (ORDER BY mass DESC) rk
      4  FROM fb) r
      5  WHERE r.rk <=trunc(&rw_ct/2);


    ID_FOOTBALLPLAYER       MASS
    ----------------- ----------
                    3         90
                    7         90
                    1         80


    SQL>
     
     
    monkey likes this.
  7. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!!!
    David, I opwe U for that!
    But surely, have a huge coup of questions!
    First, what about:
    Code (SQL):
    SQL>
    Where do U write programs? It does not look as sql developer.

    Many thanks!!!
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    SQL*Plus, I'm a DBA, not a developer so I rarely use SQL Developer for such things.
     
  9. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    It's SQl Plus