sql - Autoincrement based on a set of other columns -

I have a table course and every course has many resources. The

  course === ======= course_id Resources ========== course_id number  

I'll course_id Want something like a different autoincrement or, in other words, I want to calculate the resources for a given course. For example, the resource table may look like this:

  course_id | Number ================== 1 | 1 1 | 2 2 | 1 1 | 3 1 4 2 2 2 | 3  

and so on. I want to use it in in SQL , IBM DB2.

should Resources Choose your own ID ...

  Enter the resource_id. Course_id ======================= 1 | 1 2 | 1 3 | 2 4 | 1 5 | 1 6 | 2 7 | 2  

... and search rank through query:

  SELECT course_id, (SELECT COUNT (*) resource WHERE Snsadn_aidi & lt; r.resource_id) + 1 resource AR rank p  

it's certainly more work, but will dramatically simplify life in every other respect (such as adapter, Dealing with extinction).