Enumerating groups in a query
I'm trying to enumerate the groups inside a certain view.
Example, from this view:
ID FK1 FK2 FK3 FK4
104091 10160 1 1 29152
106797 10161 2 1 NULL
34682 10162 1 6 28556
61582 10162 1 6 28557
53045 10163 1开发者_如何学Go 1 29154
23918 10164 1 6 28731
34683 10164 1 6 28729
89003 10164 1 4 28728
50547 10164 1 4 28727
50548 10165 1 6 29846
34684 10165 1 5 29847
23919 10165 1 5 29844
I'd like to have something like:
ID FK1 FK2 FK3 FK4 C
104091 10160 1 1 29152 1
106797 10161 2 1 NULL 2
34682 10162 1 6 28556 3
61582 10162 1 6 28557 3
53045 10162 1 1 29154 4
23918 10164 1 6 28731 5
34683 10164 1 6 28729 5
89003 10164 1 4 28728 6
50547 10164 1 4 28727 6
50548 10165 1 6 29846 7
34684 10165 1 5 29847 8
23919 10165 1 5 29844 8
As you can see, it looks like a
ROW_NUMBER() OVER (PARTITION BY FK1, FK2, FK3 ORDER BY FK1, FK2, FK3) AS C
With the only difference that PARTITION BY resets the row_number for each group. Instead, I'd like to have a unique number for each group. More like a GROUP_NUMBER()
I hope the question was clear enough :)
DENSE_RANK() OVER (ORDER BY FK1, FK2, FK3) AS C
Should do what you need.
精彩评论