Get highest value from every group

Question

I have table: Id, Name, Account, Date, ItemsToSend

I want to group rows by Name and Account
From each group I want to get elements with latest Date
And display element's Name, Account and ItemsToSend

I managed something like this:

select
    Name,
    Account,
    max(Date),
    max(CountItemsSend)
from History
where
    Date = (
        select max(Date)
        from History as p
        where
            p.Account = History.Account
            and p.Name = History.Name
    )
group by
    Name,
    Account

I am afraid of max(Date), max(CountItemsSend). I dont think it is ok. After where there is only 1 result for each group, so what is the point of max use there?

Edit the question add some sample data & desired result would helpful. — Yogesh Sharma, Mar 04 '19 at 15:58
"I don't think it is ok". What do you mean? That's the purpose of aggregation. In this case, for each `Name, Account` group, there should only be one `MAX()`. — Eric, Mar 04 '19 at 16:46
Possible duplicate of [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Vladimir Baranov, Mar 05 '19 at 00:14

Gordon Linoff · Answer 1 · 2019-04-04T10:24:04.347

0

You don't need aggregation. Just:

select h.*
from History h
where h.Date = (select max(h2.Date)
                from History h2
                where h2.Account = h.Account and
                      h2.Name = h.Name
               );

edited Apr 04 '19 at 10:24

answered Mar 04 '19 at 16:26

Gordon Linoff

1,242,037
58
646
786

I am getting error: `An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.` – dafie Apr 04 '19 at 07:21

Zhorov · Answer 2 · 2019-03-05T10:11:31.203

Another possible approach is to use ROW_NUMBER() to number rows grouped by name and account and ordered by date descending and then select the rows with number equal to 1. These rows are with max Date per group and CountItemsSend from the same row.

SELECT
    t.[Name],
    t.[Account],
    t.[Date],
    t.[CountItemsSend]
FROM (
   SELECT
      [Name],
      [Account],
      [Date],
      [CountItemsSend],
      ROW_NUMBER() OVER (PARTITION BY [Name], [Acount] ORDER BY [Date] DESC) AS Rn
   FROM History
) t
WHERE t.Rn = 1

score 0 · Answer 3 · answered Mar 05 '19 at 02:53

A CTE can make this neater.

WITH maxDates as
(
    SELECT Name, Account, MAX(Date) as LatestDate
    FROM History
    GROUP BY Name, Account
)

SELECT h.Name, h.Account, h.Date, h.CountItemsSend
FROM History h
INNER JOIN maxDates m
    on m.Name = h.Name and m.Account = h.Account and m.LatestDate = h.Date

Get highest value from every group

3 Answers3