|
View previous topic :: View next topic
|
| Author |
Message |
Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
Hi everybody,
a question concerning group changes and numbering the groups:
We have a table with some more than three coloums but COL2 and COL3:
| Code: |
COL1 COL2 COL3
01 One Alpha
02 Two Bravo
03 Two Charlie
04 Three Delta
05 Three Echo
06 Three Foxtrot
07 Four Golf
08 Four Hotel
09 Four India
10 Four Juliet
11 Five Kilo
12 Five Lima
13 Five Mike
14 Five November
15 Five Oscar
16 Six Papa
17 Six Quebec
18 Six Romeo
19 Six Sierra
20 Six Tango
21 Six Uniform
22 Seven Victor
23 Seven Whisky
24 Seven X-Ray
25 Seven Yankee
26 Seven Zulu |
I'd like to select the data not only with a row number partitioned by the group but also by a group number so that the result looks like
| Code: |
COL1 COL2 COL3 GRU_NUM ROW_NUM
11 Five Kilo 1 1
12 Five Lima 1 2
13 Five Mike 1 3
14 Five November 1 4
15 Five Oscar 1 5
07 Four Golf 2 2
08 Four Hotel 2 3
09 Four India 2 4
10 Four Juliet 2 6
01 One Alpha 3 1
22 Seven Victor 4 1
23 Seven Whisky 4 2
24 Seven X-Ray 4 3
25 Seven Yankee 4 4
26 Seven Zulu 4 5
16 Six Papa 5 1
17 Six Quebec 5 2
18 Six Romeo 5 3
19 Six Sierra 5 4
20 Six Tango 5 5
21 Six Uniform 5 6
04 Three Delta 6 1
05 Three Echo 6 2
06 Three Foxtrot 6 3
02 Two Bravo 7 1
03 Two Charlie 7 2 |
It's easy to calculate the coloumn ROW_NUM by using the ROW_NUMBER() OVER (PARTITION BY COL2) clause.
But does anybody know an easy way to calculate the group numbers?
I was successful using an inner join and a double sub-select:
| Code: |
SELECT a.col2
, a.col3
, b.gru_num
, ROW_NUMBER() OVER (PARTITION BY a.col2
ORDER BY a.col2
, a.col3)
AS row_num
FROM my_table a
, (SELECT col2
, ROW_NUMBER ()
OVER (ORDER BY col2)
AS gru_num
FROM (SELECT DISTINCT col2
FROM my_table ) ) b
WHERE a.col2 = b.col2
WITH UR
; |
But - is this really the most 'elegant' way? Does it really has to be that tricky?
Thank you very much for your hints |
|
| Back to top |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| Quote: |
| But - is this really the most 'elegant' way? Does it really has to be that tricky? |
No
| Quote: |
| But does anybody know an easy way to calculate the group numbers? |
| Code: |
| DENSE_RANK() OVER ( ORDER BY col2) as GRU_NUM |
|
|
| Back to top |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
Hey, great. It works.
Thank you very much.
Tried RANK() but was not happy with that.
Sometimes it's pretty helpful to continue RTFM... |
|
| Back to top |
|
|
|
|
