Use DENSE_RANK() to get the top rank : DENSE_RANK « Analytical Functions « Oracle PL / SQL

Home
Oracle PL / SQL
1.Aggregate Functions
2.Analytical Functions
3.Char Functions
4.Constraints
5.Conversion Functions
6.Cursor
7.Data Type
8.Date Timezone
9.Hierarchical Query
10.Index
11.Insert Delete Update
12.Large Objects
13.Numeric Math Functions
14.Object Oriented Database
15.PL SQL
16.Regular Expressions
17.Report Column Page
18.Result Set
19.Select Query
20.Sequence
21.SQL Plus
22.Stored Procedure Function
23.Subquery
24.System Packages
25.System Tables Views
26.Table
27.Table Joins
28.Trigger
29.User Previliege
30.View
31.XML
Oracle PL / SQL » Analytical Functions » DENSE_RANK 




Use DENSE_RANK() to get the top rank
 


SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(20 BYTE),
  4    Last_Name          VARCHAR2(20 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(20 BYTE),
  9    Description        VARCHAR2(80 BYTE)
 10  )
 11  /

Table created.

SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56'Toronto',  'Programmer')
  3  /

row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 2234.78'Vancouver','Tester')
  3  /

row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 2324.78'Vancouver','Tester')
  3  /

row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 3334.78'Vancouver','Manager')
  3  /

row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 4334.78'Vancouver','Tester')
  3  /

row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('06','Linda',    'Green',   to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 5334.78,'New York',  'Tester')
  3  /

row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('07','David',    'Larry',   to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 6334.78,'New York',  'Manager')
  3  /

row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('08','James',    'Cat',     to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 7334.78,'Vancouver', 'Tester')
  3  /

row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select from Employee
  2  /

ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY                 DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- -------------------- --------------------------------------------------------------------------------
01   Jason                Martin               25-JUL-96 25-JUL-06    1234.56 Toronto              Programmer
02   Alison               Mathews              21-MAR-76 21-FEB-86    2234.78 Vancouver            Tester
03   James                Smith                12-DEC-78 15-MAR-90    2324.78 Vancouver            Tester
04   Celia                Rice                 24-OCT-82 21-APR-99    3334.78 Vancouver            Manager
05   Robert               Black                15-JAN-84 08-AUG-98    4334.78 Vancouver            Tester
06   Linda                Green                30-JUL-87 04-JAN-96    5334.78 New York             Tester
07   David                Larry                31-DEC-90 12-FEB-98    6334.78 New York             Manager
08   James                Cat                  17-SEP-96 15-APR-02    7334.78 Vancouver            Tester

rows selected.

SQL>
SQL>
SQL> SELECT id, first_name, salary,
  2    DENSE_RANK() OVER(ORDER BY salary desctoprank
  3  FROM employee;

ID   FIRST_NAME               SALARY    TOPRANK
---- -------------------- ---------- ----------
08   James                   7334.78          1
07   David                   6334.78          2
06   Linda                   5334.78          3
05   Robert                  4334.78          4
04   Celia                   3334.78          5
03   James                   2324.78          6
02   Alison                  2234.78          7
01   Jason                   1234.56          8

rows selected.

SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /

Table dropped.

SQL>
SQL>
SQL>
SQL>
SQL>
           
         
  














Related examples in the same category
1.DENSE_RANK()rank items, leaves no gaps when there is a tie
2.Deal with Null in Rank
3.Deal with Null in dense_rank
4.DENSE_RANK() with NULLS FIRST
5.DENSE_RANK() with NULLS LAST
6.DENSE_RANK function
7.Compare rank() and dense_rank()
8.dense_rank() over (order by comm desc nulls last)
9.Use rank and dense_rank in subquery
10.decode dense_rank
11.dense_rank() over ( partition by deptno order by sal desc )
12.rank and dense_rank with group by
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.