How do I find corresponding row data based on max column values?

Question

I want to take the max value of each partitioned block and find the correlating id(in the same row). I then want to use the singular show_id as the 'winner' and bool_flag all rows in the same partition with a matching show_id.

I am having trouble implementing this, especially the window function-- I have hit multiple issues saying that the subquery is not supported, or "must appear in the GROUP BY clause or be used in an aggregate function sql"

subQ1 as (
select subQ0.*,
   case

   **when show_id = 
   (select id from (select show_id, max(rn_max_0)
   over (partition by tv_id, show_id)))** 

   then 1
   else 0
   end as winner_flag

from subQ0

)

What I have:

tv_id     show_id       partition_count
1           42              1
1           42              2
1           42              3
1           7               1
2           12              1
2           12              2
2           12              3
2           27              1

What I want:

tv_id     show_id       partition_count      flag    
1           42              1                  1
1           42              2                  1
1           42              3                  1
1           7               1                  0
2           12              1                  1
2           12              2                  1
2           12              3                  1
2           27              1                  0

Because tv_id 1 has the most connections to show_id 42, those rows get flagged.

Ideally, something similar to SQL select only rows with max value on a column, but the partitions and grouping have led to issues. This dataset also has billions of rows so a union would be a nightmare.

Thanks in advance!

sorry! was editing the post-- i included sample data and expected result. — ss1731, Jun 13 '19 at 21:20

score 0 · Accepted Answer · answered Jun 13 '19 at 21:42

For each tv_id, you seem to want the show_id that appears the most. If so:

select s.*,
       (case when cnt = max(cnt) over (partition by tv_id)
             then 1 else 0
        end) as flag
from (select s.*, count(*) over (partition by tv_id, show_id) as cnt
      from subQ0 s
     ) s;

How do I find corresponding row data based on max column values?

1 Answers1