You can use a ranking function like row_number to get your result. Post some sample data along with your table structures and ecpexcted result.

Thanks.