dense_rank()

Good url about dense_rank()

https://www.sqlservertutorial.net/sql-server-window-functions/sql-server-dense_rank-function/#:~:text=The%20DENSE_RANK()%20is%20a,they%20have%20the%20same%20values.

-- In addition to dense_rank(), apply row_number for each row

 select *, row_number() over (partition by category_id order by category_id) row_num from (

select category_id, product_id, product_name, list_price, dense_rank() over (partition by category_id order by list_price desc) price_rank from production.products

) t where price_rank < 3;

-- Top 1 record for each category by price

select * from (

select *, row_number() over (partition by category_id order by category_id) row_num from (

select category_id, product_id, product_name, list_price, dense_rank() over (partition by category_id order by list_price desc) price_rank from production.products

) t where price_rank < 3

) t2 where row_num in (1);