CODEWITHSUNDEEP
sqlpostgresql
wickys
wickys

Reputation: 13

Finding maximum value of group of rows POSTGRESQL

Determine name, party and years served of the presidents with the most years of tenure in their party. I can't use the join keyword.

Table president:

Name         | Party       | years_served|
-------------+-------------+-------------+
Roosevelt    | Democratic  | 12
Madison      | Demo-rep    | 8
Monroe       | Demo-rep    | 8
Adams        | Demo-rep    | 4

So the result would be Roosevelt, madison and monroe show up and adams doesn't because he didn't serve 8 years which is the max for demo-reps.

Upvotes: 1

Views: 38

Answers (2)

roman
roman

Reputation: 117345

You can use dense_rank() window function:

with cte as (
    select
        *, dense_rank() over(partition by Party order by years_served desc) as rnk
    from Table1
)
select
    Name, Party, years_served
from cte
where rnk = 1

sql fiddle demo

Upvotes: 1

ericpap
ericpap

Reputation: 2937

Try this:

SELECT Name 
FROM President pr 
  INNER JOIN (SELECT Party, MAX(years_served) as YS FROM president GROUP BY Party) as MaxYS on MaxYS.Party=Pr.Party 
WHERE pr.years_served>=MaxYS.YS

Hope is what you are looking for.

Upvotes: 0

Related Questions