Get a count based on the row order

Viewed 94

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.

3 Answers
Related