Help wanted: How to find out the relative position of two records in a table

I'm looking for alternatives for the following problem :   

I have a table with 4 columns ;
PNR, ENR, INVNR and RM. (note: to explain the problem, the datamodel is simplified).   

  Example of the table:

  PNR	ENR	INVNR	RM
  AB	001	1	blue
  AB	001	2	yellow
  AB	001	3	red
  AB	002	1	red
  AB	002	2	blue
  CD	001	1	green
  CD	001	2	red
  CD	002	1	yellow
  CD	002	2	blue
  

I want to do the following:
I want to find out the relative position of two particular rm's, say blue and red, for specific prn and enr. The absolute position is given by invnr.
For example I want to know for which ENR red comes after (has higher INVNR than) blue for PNR='AB'.
This problem means that rows from the same table have to be compared. I came up with the following solution:   

  SELECT DISTINCT A.ENR FROM TS A, TS B
  WHERE A.PNR='AB'

  AND A.RM='red' AND B.RM='blue'
  AND A.INVNR > B.INVNR
  AND A.ENR=B.ENR
  

This self join works well with this small table, but the real table is much larger. Since it has 107000 rows momentarily, the self join would produce 1.2 * 10^10 rows!
In combination with the other search criteria (PNR, RM) this gives unacceptably large execution times.   

Is there another way to solve this problem?    Received on Sun Nov 10 1996 - 00:00:00 CET