英文:
SQL: How to select Row that has youngest Date OR timestamp
问题
我有一个包含三列的表格，这是一个带有内连接的选择的结果：
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';
现在，我需要获取所有行的三列中的最新/最年轻/最高值。因此，结果应该只是一个日期。这可能会有问题，因为createTimestamp列是一个时间戳，而另外两列是日期。
在Oracle数据库中，有没有一种方法可以做到这一点（性能非常重要）？可以在另一个选择中使用我的第一个选择的结果吗？
谢谢
英文:
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
答案1
得分: 1
查看GREATEST函数。
SELECT GREATEST(CAST(createTimestamp AS DATE), paymentDate, buyDate)
FROM ARC_PAYMENTS
INNER JOIN PERSON_IN_PAYMENT ON ARC_PAYMENTS.PERSON_ID=PERSON_IN_PAYMENT.ID
WHERE PERSON_IN_PAYMENT.COMPANY_ID='1234567';
英文:
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';
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论