ABAP SELECT Statement to return internal table with row numbers (maybe even grouped)

[JaySchwendemann]

Hi all,

I have a situation where I have the following database table

Id   Level
0815 A
0816 A
0817 B

I am currently selecting from this db table filling a columnt with a constant literal like so

DATA(fixed_row_count) = 1.

SELECT level, @fixed_row_count AS row_count
  FROM dbtab
  INTO TABLE @DATA(result).

This results in a internal table like so

Level Row_count
A     1
A     1
B     1

I'd like to know if there is any chance to fill the row_count within the SELECT statement like so...

Level Row_count
A     1
A     2
B     3

... or even grouped on the level like so

Level Row_count
A     1
A     2
B     1

I know this is solvable in a loop, or expect it to be solveable in a SELECT ... ENDSELECT statement. And of course, solving this in code wouldn't be a biggie. Just being curious, if there are solutions to this within the "[Open] SQL realm".

Cheers

Jens

PS: We are on NW 7.50 but I surely would not want to dismiss solutions only possible in newer releases, cause it's kind of a curiousity question anyways.

[JaySchwendemann]

Hi all,

I have a situation where I have the following database table

Id   Level
0815 A
0816 A
0817 B

I am currently selecting from this db table filling a columnt with a constant literal like so

DATA(fixed_row_count) = 1.

SELECT level, @fixed_row_count AS row_count
  FROM dbtab
  INTO TABLE @DATA(result).

This results in a internal table like so

Level Row_count
A     1
A     1
B     1

I'd like to know if there is any chance to fill the row_count within the SELECT statement like so...

Level Row_count
A     1
A     2
B     3

... or even grouped on the level like so

Level Row_count
A     1
A     2
B     1

I know this is solvable in a loop, or expect it to be solveable in a SELECT ... ENDSELECT statement. And of course, solving this in code wouldn't be a biggie. Just being curious, if there are solutions to this within the "[Open] SQL realm".

Cheers

Jens

PS: We are on NW 7.50 but I surely would not want to dismiss solutions only possible in newer releases, cause it's kind of a curiousity question anyways.

[FredericGirod]

your first question, the answer is : you have nothing to do, it is the index of the internal table.

SY-TABIX when you will perform a LOOP

[ChristianGnter]

Hi Jens,

yes this is possible in newer Netweaver releases (>=753) with so called window functions. ROW_NUMBER does the job.

E.g.

  SELECT FROM t000
    FIELDS 
    ROW_NUMBER( ) OVER( ORDER BY mandt ) AS row_count,  
    t000~mandt  
    ORDER BY mandt INTO TABLE @DATA(table).

=>

Table
ROW_COUNT MANDT
1 000
2 100
3 450

Grouping is also possible with PARTITION.

BR Christian

https://help.sap.com/doc/abapdocu_755_index_htm/7.55/en-US/abensql_win_func.htm

[ChristianGnter]

Example for PARTITION

    SELECT
      FROM
        t100
      FIELDS
        ROW_NUMBER( ) OVER( PARTITION BY arbgb ORDER BY msgnr ) AS row_count,
        t100~arbgb,
        t100~msgnr
      WHERE sprsl = @sy-langu
      ORDER BY arbgb, msgnr
      INTO TABLE @DATA(table)
      UP TO 1000 ROWS.

[thkolz]

Interesting, but I'm really wondering about the added value...

PARTITION might be useful in rare cases.

[JaySchwendemann]

Awesome, christian.guenter ♥