I have a table with a set of business dates I need to select the max date per month and year tried using the last_day function but that returns the last day not the max date of that month.please help me out.
我有一个包含一组业务日期的表我需要选择每月的最大日期和年份尝试使用last_day函数但返回的是最后一天而不是该月的最大日期。请帮助我。
1 个解决方案
#1
2
MAX is an aggregate function, so you need to figure out how to group all of the days of the month together. The easiest way to do that is apply a function that will return the same value for every day in that month. LAST_DAY would work, but I prefer TRUNC (with 'MM' specified).
MAX是一个聚合函数,因此您需要弄清楚如何将该月的所有日期组合在一起。最简单的方法是应用一个函数,该函数将在该月的每一天返回相同的值。 LAST_DAY会起作用,但我更喜欢TRUNC(指定'MM')。
SELECT MAX(your_column) FROM your_table GROUP BY TRUNC(your_column, 'MM')
#1
2
MAX is an aggregate function, so you need to figure out how to group all of the days of the month together. The easiest way to do that is apply a function that will return the same value for every day in that month. LAST_DAY would work, but I prefer TRUNC (with 'MM' specified).
MAX是一个聚合函数,因此您需要弄清楚如何将该月的所有日期组合在一起。最简单的方法是应用一个函数,该函数将在该月的每一天返回相同的值。 LAST_DAY会起作用,但我更喜欢TRUNC(指定'MM')。
SELECT MAX(your_column) FROM your_table GROUP BY TRUNC(your_column, 'MM')