SQL Server/T-SQL Tutorial/Analytical Functions/DENSE RANK

DENSE_RANK() OVER

   <source lang="sql">

5> create table department( 6> dept_name char(20) not null, 7> emp_cnt int not null, 8> budget float, 9> date_month datetime); 10> GO 1> 2> insert into department values("Research", 5, 50000, "01.01.2002"); 3> insert into department values("Research", 10, 70000, "01.02.2002"); 4> insert into department values("Research", 5, 65000, "01.07.2002"); 5> insert into department values("Accounting", 5, 10000, "01.07.2002"); 6> insert into department values("Accounting", 10, 40000, "01.02.2002"); 7> insert into department values("Accounting", 6, 30000, "01.01.2002"); 8> insert into department values("Accounting", 6, 40000, "01.02.2003"); 9> insert into department values("Marketing", 6, 10000, "01.01.2003"); 10> insert into department values("Marketing", 10, 40000, "01.02.2003"); 11> insert into department values("Marketing", 3, 30000, "01.07.2003"); 12> insert into department values("Marketing", 5, 40000, "01.01.2003"); 13> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> SELECT DENSE_RANK() OVER(ORDER BY budget DESC) AS rank_budget, 4> ROW_NUMBER() OVER(ORDER BY budget DESC) AS row_number,dept_name, emp_cnt, budget 5> FROM department 6> WHERE budget <= 50000; 7> GO rank_budget row_number dept_name emp_cnt budget

---

-------------------- -------------------- ----------- ------------------------

                  1                    1 Research                       5                    50000
                  2                    2 Accounting                    10                    40000
                  2                    3 Accounting                     6                    40000
                  2                    4 Marketing                     10                    40000
                  2                    5 Marketing                      5                    40000
                  3                    6 Marketing                      3                    30000
                  3                    7 Accounting                     6                    30000
                  4                    8 Accounting                     5                    10000
                  4                    9 Marketing                      6                    10000

(9 rows affected) 1> 2> drop table department; 3> GO 1> 2> 3></source>