0

how can i do something like this:

I have two tables, A and B. A --> B is one to many.

B has a varchar field that we will call field1.

I have also a sequence seq1 that i want to use in this way:

Suppose we have A1 and A2, two records belonging to table A. A1 has (B1,B2,B3,B4) and A2 has (B5,B6).

I want to use the sequence for each group and start from the beginning each time i change group to update field1 in B. So i will have somthing like

B1.field1 = 1, B2.field1 = 2, B3.field1 = 3, B4.field1 = 4

now the sequence start back from 1 for A2:

B5.field1 = 1, B6.field2 = 2.

Is there some complex nested structure to do that or i need a function?

I was thinking about using a temporary table ,playing a bit on indexes and sub countings but i don't manage to find a wayout.

Thanks

tadman
  • 200,744
  • 21
  • 223
  • 248
Davide Barbieri
  • 5
  • 6
  • 3
    Sample data and desired results would really help. – Gordon Linoff May 10 '18 at 15:15
  • Use a user-defined variable in a join, just like you would to generate per-group ranking in https://stackoverflow.com/questions/532878/how-to-perform-grouped-ranking-in-mysql – Barmar May 10 '18 at 15:21
  • StackOverflow is not a free coding service. SO expects you to [try to solve your own problem first](http://meta.stackoverflow.com/questions/261592). Please update your question to show what you have already tried in a [mcve]. For further information, please see [ask], and take the [tour] :) – Barmar May 10 '18 at 15:22
  • Then you simply set `field1` to the rank variable. – Barmar May 10 '18 at 15:22

0 Answers0