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>
| 14.3.DENSE_RANK |
| 14.3.1. | DENSE_RANK() OVER |