Issue with select query with MAX keyword and Group by keyword.!

[gagan_narendra]

I wrote a select query to get the latest record:

SELECT SINGLE MAX( obknr ) MAX( datum ) INTO (wa_obknr, wa_datum) FROM objk WHERE equnr = lv_equip-ast_asset_num AND taser = 'SER01' GROUP BY obknr datum.

The above query should get the latest record as shown in the image attached

But its fetching 1st record only not the second record. I need to fetch 2nd record

[raymond_giuseppi]

AFAIK you cannot use aggregate function on the group by fields, remove the max, use a order by clause and select up to 1 rows.

[Sandra_Rossi]

You didn't explain with words what you want to do, so impossible to understand what you want to achieve.

For information, this code compiles (in ABAP 7.57):

SELECT SINGLE MAX( obknr ), MAX( datum )
FROM objk 
WHERE equnr = '' AND taser = 'SER01'
INTO (@data(wa_obknr), @data(wa_datum)).

[gagan_narendra]

sandra.rossi The above code should have fetched value with date: 24.02.2023, but its fetching the value with date: 09.01.2023

[Sandra_Rossi]

I don't get what you mean, isn't it correct?

[Sandra_Rossi]

I guess I understand what you mean, but it's your screenshot which is wrong and doesn't illustrate at all what you mean.

[gagan_narendra]

sandra.rossi So the solution is to not use GROUP BY keyword when we are using aggregate keywords( like : MAX).

Thank you Sandra.

[Sandra_Rossi]

Is it a generalization that you are proposing for all cases? If so, NOT AT ALL.

If I understand well what you mean (you still didn't clarify), you are right that you must not use GROUP BY (but not because you are using MAX). Use either Raymond solution, or use a window expression, or use IN ( SELECT ... MAX ... ), etc. (many possibilities as you can see)

I guess you won't clarify so it could be something like:

SELECT SINGLE obknr, datum
    FROM objk
    WHERE equnr = '000000000100001024' AND taser = 'SER03'
      AND obknr IN ( SELECT MAX( obknr )
                    FROM objk AS objk_2
                    WHERE equnr = objk~equnr
                      AND taser = objk~taser )
    INTO (@DATA(wa_obknr), @DATA(wa_datum)).