0

Given the data set for a table person_investments:

id person stock invested
1 Jack GOOG 1000
2 Jack META 800
3 Jack TSLA 1500
4 Jill GOOG 4000
5 Jill TSLA 1000
6 Taylor WMT 1000
7 Taylor TGT 2500
8 Taylor HD 1000

I'm trying to figure out how to query and get the stock in which each person is most invested. In this example, I'm looking for this result:

id person stock invested
3 Jack TSLA 1500
4 Jill GOOG 4000
7 Taylor TGT 2500

I've been trying to work with something basic like this:

SELECT id, person, stock, MAX(invested) 
FROM person_investments
GROUP BY person;

However, with a real data set, I'm not getting the expected results. What am I getting wrong?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Austin Brown
  • 830
  • 12
  • 24

2 Answers2

1

Need to pick max in each group of person. One was can be using rank -

select id, person,stock,invested
from (
 select id, person,stock,invested, 
 dense_rank() over (partition by person order by invested desc) rnk 
 from stock)s
where s.rnk =1;

Refer fiddle here.

I've also added a scenario where there are more then 1 max.

Pankaj
  • 2,692
  • 2
  • 6
  • 18
0
SELECT id, person, stock, MAX(invested) FROM person_investments GROUP BY stock,person;

In groupby field, you have to combine person and stock also.

Kumar T
  • 1
  • 1
  • This will not work, as ID is missing from ```group by```. Adding ID as well, will not give desired results – Pankaj Jul 08 '22 at 02:56