CODEWITHSUNDEEP
sqlpostgresqlsubquerywindow-functions
Meeko
Meeko

Reputation: 11

PostgreSQL - How to use window function to pull the max value from a column

I am a beginner, so apologies in advance for simple/non-technical terminology.

I have a table where each row shows the company name, day/month/year, and how many visits they received in that day. My goal is to show which company had the highest number of visits for January 2018.

I was able to find how many visits each company received in January 2018 using this query:

select to_char(datecolumn,'Mon') as monthkey, extract(year from datecolumn) as yearkey, companyname, sum(visits) as sumvisits
from t1
where monthkey = 'Jan' and yearkey = '2018'
group by monthkey, yearkey, companyname
order by companyname

Now I need to use a window function to find the max value of sumvisits of January along with the corresponding company, but I'm stuck.

I've tried partitioning by month:

select companyname, monthkey, max(sumvisits) over (partition by monthkey) as maxvisits
from (select to_char(f_date,'Mon') as monthkey, extract(year from f_date) as yearkey, companyname, sum(visits) as sumvisits
from t1
where monthkey = 'Jan' and yearkey = '2018'
group by monthkey, yearkey, dealername
order by companyname)

But this query just gives me the max visits of one company and lists it for every company.

I don't think I should use the limit function or anything like that because the query needs to be applicable to multiple months.

I want to to see:

monthkey yearkey companyname sumvisits
Jan      2018    ABCInc     5000

Can someone please help advise what I'm doing wrong/point me in the right direction?

Upvotes: 1

Views: 123

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Just use order by and limit:

select to_char(datecolumn,'Mon') as monthkey, extract(year from datecolumn) as yearkey, companyname, 
       sum(visits) as sumvisits
from t1
where monthkey = 'Jan' and yearkey = '2018'
group by monthkey, yearkey, companyname
order by sumvisits desc
limit 1;

Upvotes: 1

Related Questions