'How to get the max datetime for particular date among multiple dates, data example is as below

Table has this value:

Heading 1 Heading 2
xyz 01-APR-2022 12.11.21.000000000 AM
xyz 01-APR-2022 12.11.29.000000000 AM
xyz 01-APR-2022 12.12.21.000000000 AM
xyz 02-APR-2022 12.09.21.000000000 AM
xyz 02-APR-2022 12.11.21.000000000 AM
xyz 02-APR-2022 12.22.21.000000000 AM

need below output:

Heading 1 Heading 2
xyz 01-APR-2022 12.12.21.000000000 AM
xyz 02-APR-2022 12.22.21.000000000 AM
sqloraclemaxinformaticainformatica-powercenter


Solution 1:[1]

informatica only solution -

  1. Create an exp transformation with three ports.
in_out_Heading1
in_out_Heading2
out_date_trunc=TRUNC(in_out_Heading2)
  1. Next, use an agg transformation with below ports.
in_out_Heading1  --group by port
in_Heading2
in_date_trunc --group by port
out_Heading2=MAX(in_Heading2)

And then connect out_Heading2 and in_out_Heading1 to your final target.

Solution 2:[2]

You can group by extracted date value, so need a conversion such as the one below

SELECT heading1, MAX(heading2)
  FROM t
 GROUP BY heading1, TRUNC(heading2)

the above SELECT statement is based on the Oracle database, you can replace TRUNC(heading2) depending on your current DBMS. The data type of heading2 is presumed to be TIMESTAMP

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Koushik Roy
Solution 2 Barbaros Özhan

Related Questions