select max

[MaDo]

How to select max second-to-last in sap ABAP query?

[Yasin]

Hi Ahmad,

what do u mean by second-to-last please clarify more

Thanks

[MaDo]

For example, I have these

11384474

11333125

11648595

11648594

11405886

11354018

11648593

11648591

27547272

27547271

27547270

27547269

I want to get the max number but the second ( 27547270 )

[Yasin]

Hi Ahmad

you can try something like

SELECT MAX( VBRK~VBELN ) INTO ( v_VBELN )
  FROM VBRK
 WHERE VBRK~VBELN < ( SELECT MAX( VBRK~VBELN )
                 FROM VBRK ).

you may need to sort Ascending or Descending you can check base on result.

Please reward if was helpful to you.

Thanks.

[MaDo]

Gave me the last number 27547269 but I need the second last 27547270

SELECT MAX( CDPOS~CHANGENR ) INTO NLL
FROM CDPOS
WHERE CDPOS~CHANGENR < ( SELECT MAX( CDPOS~CHANGENR ) FROM CDPOS )
and TABNAME = 'MVKE'
AND FNAME = 'MVGR2'
and TABKEY LIKE '%100010'
AND CDPOS~OBJECTID = MARA-MATNR.

[Yasin]

i have tested my query it is working perfect. if it is giving last number then try to run the query without the

where condition only use the MAX what value will get ?

make sure that TABKEY LIKE '%100010' is not causing any conflict. try to run the query without many condition so you can trace

[MaDo]

where condition only use the MAX what value will get ?

Yes

[Yasin]

Dear Ahmad

in your list you have the value 27547272 which is bigger than 27547269 , 27547270 , 27547271

so the max value in your list is 27547272 the second last is 27547271

Thanks

[Sandra_Rossi]

Good candidate for a Window function (many blog posts), depends which ABAP version you're running. In older ABAP versions, you can still do it with classic ABAP code. I guess your query is more complex than the one you ask (max value of all lines of the table), so if you provide it, people could better answer.

[former_member660513]

Did you try with

order by 'your_column' 
up to 
2 ROWS

and then read the second row of the result set?