CODEWITHSUNDEEP
sqlpostgresql
nip
nip

Reputation: 1697

Select max value from column for every value in other two columns

I'm working on a webapp that tracks tvshows, and I need to get all episodes id's that are season finales, which means, the highest episode number from all seasons, for all tvshows.

This is a simplified version of my "episodes" table.

 id   tvshow_id   season   epnum
 ---|-----------|--------|-------
 1  |     1     |    1   |   1
 2  |     1     |    1   |   2
 3  |     1     |    1   |   3
 4  |     1     |    2   |   1
 5  |     1     |    2   |   2
 6  |     2     |    1   |   1
 7  |     2     |    1   |   2
 8  |     2     |    1   |   3
 9  |     2     |    1   |   4
10  |     2     |    2   |   1
11  |     2     |    2   |   2

The expect output:

 id
 ---|
 3  |
 5  |
 9  |
11  |

I've managed to get this working for the latest season but I can't make it work for all seasons.

I've also tried to take some ideas from this but I can't seem to find a way to add the tvshow_id in there.

I'm using Postgres v10

Upvotes: 0

Views: 85

Answers (3)

Sabari
Sabari

Reputation: 244

Below is the simple query to get desired result. Below query is also good in performance with help of using distinct on() clause

  select
   distinct on (tvshow_id,season)
   id
  from your_table
  order by tvshow_id,season ,epnum desc

Upvotes: 0

Ajay Gupta
Ajay Gupta

Reputation: 1845

SELECT Id from
(Select *, Row_number() over (partition by tvshow_id,season order by epnum desc) as ranking from tbl)c
Where ranking=1

Upvotes: 2

Vivek
Vivek

Reputation: 803

You can use the below SQL to get your result, using GROUP BY with sub-subquery as:

select id from tab_x
where (tvshow_id,season,epnum) in (
select tvshow_id,season,max(epnum)
from tab_x
group by tvshow_id,season)

Upvotes: 2

Related Questions