0

I have a table with three columns, that ist result of a Select with an inner join:

SELECT createTimestamp, paymentDate, buyDate
FROM ARC_PAYMENTS 
INNER JOIN PERSON_IN_PAYMENT ON ARC_PAYMENTS.PERSON_ID=PERSON_IN_PAYAMENT.ID
WHERE PERSON_IN_PAYMENT.COMPANY_ID='1234567';

Now, I need to get the latest/youngest/highest value of the three columns of all rows. So the result should be just one Date. That might be problematic because the createTimestamp column is a timestamp, the other two columns are date.

Is there any way to do that (performance is very important) in a oracle database? To use the result of my first SELECT in another select?

Thank you

jarlh
  • 42,561
  • 8
  • 45
  • 63
MarkusJackson
  • 225
  • 2
  • 12
  • you need to edit your question, show **complete** table DDL, and sample data, expected output. – OldProgrammer Apr 06 '23 at 11:34
  • You want `trunc(greatest(t1, d1, d2))` ? – Ponder Stibbons Apr 06 '23 at 11:35
  • Do you mean one date per row or one date across all rows/columns? GREATEST() will give you the max value across columns and MAX will give you the max value between rows - so one or the other (or a combination of the two) should give you what you want – NickW Apr 06 '23 at 11:37
  • A [mcve] is a great start when asking for SQL assistance. – jarlh Apr 06 '23 at 11:53

1 Answers1

1

Check out the GREATEST function.

SELECT GREATEST(CAST(createTimestamp AS DATE), paymentDate, buyDate) 
FROM ARC_PAYMENTS 
INNER JOIN PERSON_IN_PAYMENT ON ARC_PAYMENTS.PERSON_ID=PERSON_IN_PAYAMENT.ID
WHERE PERSON_IN_PAYMENT.COMPANY_ID='1234567';
Koen Lostrie
  • 14,938
  • 2
  • 13
  • 19
  • I get "null" for ever line, propably because for every row one of the three attributes is null and GREATEST takes the null value as every date. Is there a possibilty to ignore null-Attributes or to handle them als smaller than a real date? – MarkusJackson Apr 11 '23 at 10:03
  • ```GREATEST``` doesn't have any options, but you could use ```COALESCE``` on the arguments as described in the 1st answer on [this similar question](https://stackoverflow.com/questions/19186283/handling-null-in-greatest-function-in-oracle). – Koen Lostrie Apr 11 '23 at 10:42