Window functions: HAVING and GROUP BY

create table ten(a int);

insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t1 (a  int, b int, c int); 

insert into t1 select a,a,a from ten;

select b,max(a) as MX from t1 group by b having MX in (3,5,7);

+------+------+

| b    | MX   |

+------+------+

|    3 |    3 |

|    5 |    5 |

|    7 |    7 |

+------+------+

3 rows in set (0.00 sec)

select b,max(a) as MX, rank() over (order by b) from t1 group by b having MX in (3,5,7);

+------+------+--------------------------+

| b    | MX   | rank() over (order by b) |

+------+------+--------------------------+

|    0 |    0 |                        1 |

|    1 |    1 |                        2 |

|    2 |    2 |                        3 |

|    3 |    3 |                        4 |

|    4 |    4 |                        5 |

|    5 |    5 |                        6 |

|    6 |    6 |                        7 |

|    7 |    7 |                        8 |

|    8 |    8 |                        9 |

|    9 |    9 |                       10 |

+------+------+--------------------------+

10 rows in set (0.00 sec)