SQL - MAX GROUP BY, Include Additional Column

Question

I have a table of purchases in the following format (simplified):

tDate	tAmount	tDesription
2021-01-01	1.50	Ice Cream
2021-01-01	1.60	Pencils
2021-02-03	4.50	Paper
2021-02-04	2.50	Staples

I'm trying to find the MAX() value of a purchase for each month, simple enough, but I can't seem to include additional columns in the result set from the row selected as the max. The output I'm looking for is:

tDate	tMonth	tYear	tAmount	tDesription
2021-01-01 00:00:00.000	January	2021	1.60	Pencils
2021-02-01 00:00:00.000	February	2021	4.50	Paper

My thoughts where that I would add in a start of the month column for each row, group by that, and apply the MAX() to the amount, along with a date filter, which works, also had to apply the tMonth and tYear in the group by.

What I've tried is:

SELECT DATEADD(MONTH, DATEDIFF(MONTH,0, [tDate]),0), FORMAT([tDate], 'MMMM') as 'Month', FORMAT([tdate], 'yyyy') as 'Year', MAX([tAmount]) as 'tAmount'

-- Source Table
FROM t

-- Last X months
WHERE [tDate] >= DATEADD(month, -6, getDate())

-- Group by the month
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH,0, [tDate]),0), FORMAT([tDate], 'MMMM'), FORMAT([tDate], 'yyyy')

-- Order
ORDER BY DATEADD(MONTH, DATEDIFF(MONTH,0, [tDate]),0) DESC

Which gives me something very close, but as soon as I add the [tDescription] column I'll receive the 'column not included in aggregate or group by' error, and I obviously can't include the column in the group by, otherwise I'll end up with a row for each.

So I'm pretty stuck on the best approach to include the [tDescription] column in the results, and I've a feeling this query is flawed, does anyone have any ideas?

How do you define *which* description to take given you *are* aggregating rows? — Dale K, Apr 13 '21 at 10:35
Are you looking for the [FIRST_VALUE](https://learn.microsoft.com/en-us/sql/t-sql/functions/first-value-transact-sql?view=sql-server-ver15) function? — Dale K, Apr 13 '21 at 10:37
Side note, `FORMAT` is a terribly show function. If you want the name of a month, use the `DATENAME` function. — Thom A, Apr 13 '21 at 10:38

score 1 · Answer 1 · answered Apr 13 '21 at 10:36

You can use window functions:

select t.*
from (select t.*,
             row_number() over (partition by year(date), month(date) order by tAmount desc) as seqnum
      from t
     ) t
where seqnum = 1;

To include the name of the month, you can add datename(month, date). However, that seems redundant with the date column.

SQL - MAX GROUP BY, Include Additional Column

1 Answers1