MySQL - priority and limit using UNION without duplicate

Question

    ID|isYN|insertDate
1 |Y    |2012-12-16
2 |Y    |2012-12-17
3 |Y    |2012-12-18
4 |N    |2012-12-30
5 |N    |2012-09-20
6 |N    |2012-11-10

I want to order table with following conditions by one query without duplicates.

1. first top two data : isYN='Y' and insertDate DESC
2. Rest of the data : insertDate DESC

Expected result :

ID|isYN|insertDate
3 |Y    |2012-12-18
2 |Y    |2012-12-17
4 |N    |2012-12-30
1 |Y    |2012-12-16
6 |N    |2012-11-10
5 |N    |2012-09-20

I tried it by using UNION and additional priority column, however it's not working as I expected. (Duplicate found)

Answer 1

On MySQL 8+, we can handle this requirement with the help of ROW_NUMBER():

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY isYN ORDER BY insertDate DESC) rn
    FROM yourTable
)

SELECT ID, isYN, insertDate
FROM cte
ORDER BY
    CASE WHEN rn <= 2 AND isYN = 'Y' THEN 0 ELSE 1 END,
    insertDate DESC;

The above sorting logic places the two most recent isYN yes records before all others. The others group is then sorted descending by the insertiom date.