Autoincrement based on a set of other columns

Question

I have a table Course and every Course has many Resources.

Course
==========
course_id

Resource
==========
course_id
number

I want something like a separate autoincrement for each course_id. Or, in other words, I want to auto-enumerate the resources for a given course. For example, the resource table could look something like:

course_id | number
==================
1         | 1
1         | 2
2         | 1
1         | 3
1         | 4
2         | 2
2         | 3

and so on. I want to do this in SQL, using IBM DB2.

Answer 1

You should let Resource pick its own ids...

resource_id | course_id
=======================
1           | 1
2           | 1
3           | 2
4           | 1
5           | 1
6           | 2
7           | 2

...and discover the rank via queries:

SELECT course_id,
       (SELECT COUNT(*)
          FROM Resource
         WHERE resource_id < r.resource_id
       ) + 1 AS rank
  FROM Resource r

This is undoubtedly more work for the optimiser, but will simplify life dramatically in every other respect (e.g., handling deletions).