Complex request SQL : GROUP BY and MAX of MAX

Question

i try to write a sql order like this subject: GROUP BY with MAX(DATE). But harder For Example:

column1    column2     column3     column4     column5
12          1          A1          1           1
0           1          A1          1           2
18          1          A1          1           3
20          1          A1          2           1
23          1          A1          2           2
4           1          B1          1           1
7           1          B1          1           2
12          1          B1          1           3
17          1          B1          2           1
0           1          B1          2           2
#\n just for clarity
13          2          A1          1           1
25          2          A1          1           2
24          2          A1          1           3
25          2          A1          2           1
0           2          A1          2           2
13          2          B1          1           1
0           2          B1          1           2
24          2          B1          1           3
25          2          B1          2           1
0           2          B1          2           2
#\n just for clarity
7           3          A1          1           1
10          3          A1          1           2
14          3          A1          1           3
15          3          A1          2           1
18          3          A1          2           2
7           3          B1          1           1
10          3          B1          1           2
14          3          B1          1           3
15          3          B1          2           1
18          3          B1          2           2

for each group in column2 for each group in column3, collect the max of column4 of max of column5 but ignore if column1 = 0 (if the max is 0 take the second max)

in this example i desire this result:

column1    column2     column3     column4     column5
23          1          A1          2           2
17          1          B1          2           1
25          2          A1          2           1
25          2          B1          2           1
18          3          A1          2           2
18          3          B1          2           2

Could you help me please

your description doesn't fir your result, there is the partition column2 and column 3 and only the max of column1 — nbk, Mar 24 '23 at 21:56
it's normal of max in column1 because in each column3 for each column2, numbers are ascending. But ignore the column1 , juste don't take the 0 in column1 — BabaBoy, Mar 24 '23 at 22:07
even so 2 and 2 for column 4 and 5 is not the maximum 1 and 3 can also be the maximum# — nbk, Mar 24 '23 at 22:26
Take the most voted answer from linked post, change analytic function a bit (partition by two columns, order by two columns desc) and add filter for column1 = 0. — Ponder Stibbons, Mar 24 '23 at 22:31

score 0 · Answer 1 · answered Mar 24 '23 at 22:33

That’s a kind of top-1-per-group problem where you need filtering first:

select *
from (
    select t.*,
        rank() over(
            partition by col2, col3
            order by col4 desc, col5 desc
        ) rn
    from mytable t
    where col1 > 0
) t
where rn = 1

The subquery filters out rows whose col1 has value 0, and enumerates groups of col2/col3 records by descending col4/col5. The outer query filters on the top record per group.

Complex request SQL : GROUP BY and MAX of MAX

1 Answers1