cancel
Showing results for 
Search instead for 
Did you mean: 

Select single with max , get record from 2 tables

0 Kudos

Hello professionals,

i have two tables like this:

I want to write Sql query to get the record with the MAX begda.

(25/10/2022)

single record only from all tables.

I tried to do something like this

  SELECT
MAX( BEGDA )
massn
pernr
FROM (
SELECT pernr begda massn
FROM pa0000
UNION ALL
SELECT pernr begda massn FROM pa0302) AS a
INTO CORRESPONDING FIELDS OF TABLE gt_pa0000
UP TO 1 ROWS.

and i tried with CASE:

  SELECT a~pernr
a~massn
a~begda
FROM pa0000 as a LEFT JOIN pa0302 AS b ON a~pernr = b~pernr
CASE a~begda > b~begda THEN a~begda
ELSE a~begda < b~begda THEN b~begda END.


:it's not work.

please help me 🙂

thanks.

Accepted Solutions (0)

Answers (1)

Answers (1)

lenapadeken
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi nextmedia4u58,

You can achieve the result by using common table expressions. For example:

WITH
  +max AS (
    SELECT FROM pa0000 AS a
    FIELDS a~pernr,
           a~massn,
           a~begda
    UNION
    SELECT FROM pa0302 AS b
    FIELDS b~pernr,
           b~massn,
           b~begda )
           
SELECT SINGLE FROM +max
       FIELDS pernr, massn, MAX( begda ) AS max
       GROUP BY begda, pernr, massn
       INTO @FINAL(result).

Best regards,

Lena