topic Re: SELECT MAX(...) ... INTO TABLE Whats wrong with this ? in Application Development and Automation Discussions

SELECT MAX(...) ... INTO TABLE Whats wrong with this ?

Former Member — Mon, 05 Jul 2010 08:38:36 GMT

Hi,

SELECT - aggregate with max:

Selecting max from an database table from a group of rows into an internal table :

Whats wrong with this ?

DATA: t_table TYPE TABLE OF /BIC/t_table.

SELECT column_a

MAX( column_b)

FROM /BIC/t_table.

INTO TABLE t_table

GROUP BY column_a.

ST22 gives: DBIF_RSQL_INVALID_RSQ

ThanXs

Martin

Re: SELECT MAX(...) ... INTO TABLE Whats wrong with this ?

Former Member — Mon, 05 Jul 2010 10:14:12 GMT

Martin,

try this

DATA: t_table TYPE TABLE OF /BIC/t_table.

SELECT column_a

MAX( column_b)

FROM /BIC/t_table.

INTO corresponding fields of TABLE t_table

GROUP BY column_a.

Thanks

Bala Duvvuri

Re: SELECT MAX(...) ... INTO TABLE Whats wrong with this ?

Former Member — Mon, 05 Jul 2010 10:27:40 GMT

Hi Martin,

you need to specify target for MAX( column_b).

try this:

types: begin of ty_tab,
        column_a type SOME_TYPE,
        max type some_type,
       end of ty_tab.

data t_table TYPE TABLE OF ty_tab.

SELECT column_a
MAX( column_b ) as max
FROM /BIC/t_table
INTO TABLE t_table
GROUP BY column_a.

regards

rea

Re: SELECT MAX(...) ... INTO TABLE Whats wrong with this ?

Former Member — Mon, 05 Jul 2010 11:18:05 GMT

erm guys isnt this obvious or am i just dumbing down something that seems clear to me?

Select MAX gets you the maximum value of that field over the whole table.

So Select MAX gets you ONE value.

it doens make sense to select one value into a table.

i would do follwwing:


DATA: t_table TYPE TABLE OF /BIC/t_table,
            lv_max type kbetr,
            wa_table type /bic/_t_table.

SELECT column_a
FROM /BIC/t_table.
INTO TABLE t_table
GROUP BY column_a.

SELECT MAX( column_b)
FROM /BIC/t_table.
INTO lv_max.

loop at t_table into wa_table.
  t_table-column_b = lv_max.
  modify t_table from wa_table index sy-tabix.
endloop.

Re: SELECT MAX(...) ... INTO TABLE Whats wrong with this ?

Former Member — Mon, 05 Jul 2010 11:31:30 GMT

Hi Florian,

SELECT column_a
MAX( column_b)
[...]
GROUP BY column_a.

above sql statement returns max value of column b grouped by column a - so result could be more than one line depending on values found in column a.

regards

rea

Re: SELECT MAX(...) ... INTO TABLE Whats wrong with this ?

former_member749376 — Mon, 23 Aug 2021 19:49:13 GMT

very good answer, I was juggling with this for a while.