But – a very similar problem, only a bit more tricky: The groups depend on chronology and can recur. Some Sample data:
Code:
ID_COL COL_1 YEAR_COL COL_2 NUM_LVL_1 NUM_LVL_2 GRP_NUM
3 Alpha 2001 One 1 1 1
5 Alpha 2002 One 2 2 1
7 Alpha 2003 One 3 3 1
12 Alpha 2004 Two 4 1 2
15 Alpha 2005 Two 5 2 2
17 Alpha 2006 Three 6 1 3
19 Alpha 2007 One 7 1 4
1 Bravo 2001 Four 1 1 1
4 Bravo 2002 Five 2 1 2
8 Bravo 2003 Six 3 1 3
11 Bravo 2004 Seven 4 1 4
13 Bravo 2005 Eight 5 1 5
2 Charlie 2001 Nine 1 1 1
6 Charlie 2002 Ten 2 1 2
9 Charlie 2003 Ten 3 2 2
10 Charlie 2004 Eleven 4 1 3
14 Charlie 2005 Nine 5 1 4
16 Charlie 2006 Eleven 6 1 5
18 Charlie 2007 Eleven 7 2 5
Note:
In COL_1 = "Alpha", COL_2 = "One" occurs three times in 2001 to 2003 and another time in 2007,
in COL_1 = "Chrarlie", COL_2 = "Nine" occures once in 2001 and once in 2005.
So in both cases groups "One" respectively "Nine" have to be counted twice.
The goal should be to receive the quantity of different groups within COL_1 (see max. of column GRP_NUM):
Code:
COL_1 Quantity
Alpha 4
Bravo 5
Charlie 5
So I’d say the most problematic thing is to calculate the GRP_NUM.
If this is done, I could use that query as a sub-select to get the second result.
1.You put the second column name on second line under first column name which don’t makes sense , repost only the relevant columns, last two looks obsolete too.
2. How you come up with GRP_NUM values? Is it required any where in the calculation to achieve what you posted ?
3 say what is input and what’s output you want and then add separately the approach you are thinking of , right now it’s all mixed, please state only the requirements first.
Joined: 11 Jan 2006 Posts: 83 Location: Lower Saxony (DE)
Only cols 2 to 4 (COL_1, YEAR_COL, COL_2) are relevant. First col ID_COL and cols 5 to 7 (NUM_LVL_1, NUM_LVL_2, GRP_NUM) are obsolete.
Input are cols 1 to 4 (ID_COL, COL_1, YEAR_COL, COL_2), cols 5 to 7 are 'hand-made' samples.
The last col GRP_NUM should be a interim result and helpful for the final result (quantity = MAX(grp_num), GROUP BY col_1)
Sorry for confusing
