0

I have a very simple query like this:

SELECT Line, ID, Enum 
  FROM tab1

the result is something like this:

enter image description here

Line  id   enum
A     10   1
A     10   2
A     10   3
A     10   4

...
B     20   4
B     20   5
B     20   6
B     20   7

...

I want, for each Line/id, the anum start by 1, just like this:

Line  id   enum
A     10   1
A     10   2
A     10   3
A     10   4
...
B     20   1
B     20   2
B     20   3
B     20   4
...

Any Idea?

Soumendra Mishra
  • 3,483
  • 1
  • 12
  • 38
Federico Cappellini
  • 1

1 Answers1

2

You need an Analytic function such as ROW_NUMBER() :

SELECT Line, ID, 
       ROW_NUMBER() OVER (PARTITION BY Line ORDER BY Enum ) AS Enum
  FROM tab1

where that function enumerates each Enum value again per each Line group starting from the integer 1, and sorted by currently existing Enum value

Perhaps you need grouping by Line and ID, then use PARTITION BY Line, ID instead of PARTITION BY Line.

Both will produce the same result in this case.

Demo

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55