SQL: retrieve the most recent entry

[tafkap95]

Hello,

I would like to retrieve the most recent entry of my data table at once.

Let's imagine I have the following table (a piece of the MSEG table):

ROWS	MATNR	CHARG	CPU_DATE	CPU_TIME
1	000012345	CA001	09.16.2024	14:00:00
2	000012345	CA002	09.16.2024	15:12:12
3	000012345	BO256	03.03.2024	23:12:00
4	000012345	CU123	09.17.2024	00:34:37
5	000012345	RA698	27.06.2024	08:00:00
6	000065887	TT0001	23.03.2023	05:32:00
7	000065887	YU3654	01.01.2024	07:12:00
8	000065887	PP5546	27.12.2023	15:23:00

For article 000012345 I wanted to retrieve line 4 from an internal table and for article 000065887 line 7, which are the most recent records by comparison of the CPU_DATE/CPU_TIME fields.

Thanks for any help.

[RaymondGiuseppi]

What did you already try, some subquery or ?

SELECT matnr, charg, cpudt_mkpf, cputm_mkpf
       FROM mseg AS m
       WHERE m~cpudt_mkpf EQ 
           ( SELECT MAX( cpudt_mkpf ) FROM mseg WHERE matnr EQ m~matnr )
         AND m~cputm_mkpf EQ 
           ( SELECT MAX( cputm_mkpf ) FROM mseg WHERE matnr EQ m~matnr AND cpudt_mkpf = m~cpudt_mkpf )
       INTO CORRESPONDING FIELDS OF TABLE @mytab.

[Sandra_Rossi]

Modern way (>= 7.54): window expression - ABAP Keyword Documentation (sap.com).

There's still the older way (join on itself via EXISTS, take MAX), simplified version: SELECT matnr, cpudate FROM mseg AS alias WHERE EXISTS IN ( SELECT matnr, MAX(cpudate) FROM mseg GROUP BY matnr WHERE matnr = alias~matnr).

[tafkap95]

Hi Sandra, 

I really like the idea of the Modern Way, it's always interesting to learn new features, I'll take a closer look at that.