[RESOLVED] Can I do this in a query?

Bjom

I need some sort of successive count on the assessorID ...

Here is an example how the table looks like, (a third field would contain the rating, but it doesn't matter for what I want to achieve)

exerciseID, assessorID
E1|A
E1|B
E1|C
E2|D
E2|E
E2|F
E3|A
E3|C
E3|E
E3|B
E4|B
E4|D

I'm not interested in who the individual assessor was (there were plenty) I simply need to distinguish the observations by the "number" or whatever you would call that, reducing the distinct "categories" to 2-4 in the example above. The result should look like this:

exerciseID, observationNo
E1|1
E1|2
E1|3
E2|1
E2|2
E2|3
E3|1
E3|2
E3|3
E3|4
E4|1
E4|2

Hope this is a clear enough description and hope there is a simple solution 🙂

Bjom

Lars_Berg

select exerciseID,
       (select count(*)
          from exercises e1
         where e1.exerciseID = e2.exerciseID
           and e1.assessorID <= e2.assessorID) as observationNo
  from exercises e2
 order by exerciseID,observationNo

Bjom

Thank you very much! This seems to work very well.

I applied it to my table adding quite a few more conditions to the WHERE part of the subselect and it produced a table that looks quite right (after 58 seconds - but that doesnt matter).

One thing that I do not feel very comfotable with, because I do not understand it well, is this part:

e1.assessorID <= e2.assessorID

The assessorID is not numeric. In my example is a single letter, in my real table it's a 32 char alphanumerical ID. I got a result - so it seems to be ok. But why?

Bjom

Weedpacket

Probably lexicographical ordering.

Bjom

probably 😉

Thanks again, I rechecked: it works perfect. This is exactly what I needed.