Frequently I need to do something in SQL Server that I just don’t know how to do properly.
Basically, if I have a table #sample that is like this:
| DEPARTMENT | DATE |
|---|---|
| ACCOUNTING | A |
| FINANCE | A |
| PAYROLL | A |
| ACCOUNTING | B |
| PAYROLL | A |
| FINANCE | A |
| PAYROLL | B |
| PAYROLL | C |
I need to query that table to get an output that is this:
| DEPARTMENT | DATE | DUPECHECK |
|---|---|---|
| ACCOUNTING | A | 1 |
| ACCOUNTING | B | 2 |
| FINANCE | A | 1 |
| FINANCE | A | 1 |
| PAYROLL | A | 1 |
| PAYROLL | A | 1 |
| PAYROLL | B | 2 |
| PAYROLL | C | 3 |
In Oracle Hyperion Brio at my old job I used to create a column that value 1 for every row and writing a function Cume on that column with a break value of the column I needed to count.
In Excel, I could create a concatenated column that has my break values, and then do a countif(concat:concat,concat1) copy that formula down.
I feel like it should be something similar to this, but that’s not it at all:
ROW_NUMBER() OVER (PARTITION BY #sample.Department ORDER BY #sample.Date) DUPECHECK
Help me out?
Advertisement
Answer
As mentioned in the comments above, ROW_NUMBER() is part of the set of ranking functions.
For your purposes, you can use the DENSE_RANK():
DENSE_RANK() OVER(PARTITION BY #sample.Department ORDER BY #sample.Date)DUPECHECK