I have a table with this structure
Create Table Example (
[order] INT,
[typeID] INT
)
With this data:
order|type
1 7
2 11
3 11
4 18
5 5
6 19
7 5
8 5
9 3
10 11
11 11
12 3
I need to get the count of each type based on the order, something like:
type|count
7 1
11 **2**
18 1
5 1
19 1
5 **2**
3 1
11 **2**
3 1
Context
Lets say that this table is about houses, so I have a list houses in an order. So I have
- Order 1: A red house
- 2: A white house
- 3: A white house
- 4: A red house
- 5: A blue house
- 6: A blue house
- 7: A white house
So I need to show that info condensed. I need to say:
- I have 1 red house
- Then I have 2 white houses
- Then I have 1 red house
- Then I have 2 blue houses
- Then I have 1 white house
So the count is based on the order. The DENSE_RANK function would help me if I were able to reset the RANK when the partition changes.