CODEWITHSUNDEEP
sqlamazon-redshift
Michelle
Michelle

Reputation: 202

Show only the max value of each group

I'm having a problem with a query that needs to show the for each group, user, status, in a day the max value of a certain field.

   groupno   user    status                      date  amount
        74  user1   status_1    2016-01-01 05:40:00.0   900
        74  user1   status_1    2016-01-01 05:45:00.0   1200
        79  user1   status_2    2016-01-01 05:45:31.0   31
        79  user1   status_2    2016-01-01 05:50:00.0   300
        79  user1   status_2    2016-01-01 05:55:00.0   600
        79  user1   status_2    2016-01-01 06:00:00.0   900
        79  user1   status_2    2016-01-01 06:05:00.0   1200
        90  user1   status_1    2016-01-01 06:07:52.0   172
        90  user1   status_1    2016-01-01 06:10:00.0   300
        90  user1   status_1    2016-01-01 06:15:00.0   600

I'm trying to use a inner join to max the max amount for each group, but is not working as I retrieve all the values again:

select t.groupno, t.user, t.status, t.date, t.amount
from ( select groupno,user, status, date, max (amount) as maxact from table group by 1,2,3,4) as m
Inner
join table as t
on t.user= m.user
and t.amount= m.maxact
and t.status = m.status
and t.date = m.date

Upvotes: 0

Views: 1773

Answers (3)

kjmerf
kjmerf

Reputation: 4335

Not sure if I completely follow the question but looks to me like you want the row with the max amount for each group. For that, you could try:

SELECT t.groupno, t.user, t.status, t.date, t.amount
FROM t
INNER JOIN
(SELECT groupno, MAX(amount) as mx
 FROM t
 GROUP BY groupno) sub
ON t.groupno = sub.groupno
WHERE t.amount = sub.mx

Upvotes: 0

Zorkolot
Zorkolot

Reputation: 2017

If you just want the max amount, with respect to the date- you can take the MAX and cast the datetime as a date:

DECLARE @temp TABLE (groupno int, [user] varchar(20), status varchar(20), date datetime, amount int)

INSERT INTO @temp
VALUES
 (74,'user1', 'status_1', '2016-01-01 05:40:00.0', 900)
,(74,'user1', 'status_1', '2016-01-01 05:45:00.0', 1200)
,(79,'user1', 'status_2', '2016-01-01 05:45:31.0', 31)
,(79,'user1', 'status_2', '2016-01-01 05:50:00.0', 300)
,(79,'user1', 'status_2', '2016-01-01 05:55:00.0', 600)
,(79,'user1', 'status_2', '2016-01-01 06:00:00.0', 900)
,(79,'user1', 'status_2', '2016-01-01 06:05:00.0', 1200)
,(90,'user1', 'status_1', '2016-01-01 06:07:52.0', 172)
,(90,'user1', 'status_1', '2016-01-01 06:10:00.0', 300)
,(90,'user1', 'status_1', '2016-01-01 06:15:00.0', 600)


SELECT groupno, [user], [status], CAST([date] as date) [Date], MAX(amount) as MAXamount
FROM @temp
GROUP BY groupno, [user], [status], CAST([date] as date)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Use window functions:

select t.*,
       max(t.amount) over (partition by groupno, user, status, date_trunc('day', date)) as max_amount
from t;

I assume that groupno/user/status/day are the dimensions for calculating the maximum.

Upvotes: 2

Related Questions