WITH 문은 테스트 용도입니다. 없다고 생각하고 이하 쿼리 형태만 봐주세요.
마지막 LIMIT 구문 2개는 페이징 쿼리를 염두해 두고 추가했습니다.
 

WITH bbs AS
(
SELECT 1 cid, '제목1' title, '내용1' contents, 'Y' noticeyn, '20221109' noticedate
UNION ALL SELECT 2, '제목2', '내용2', 'N', null
UNION ALL SELECT 3, '제목3', '내용3', 'Y', '20221109'
UNION ALL SELECT 4, '제목4', '내용4', 'N', null
)
(
SELECT cid, title, contents, noticeyn, noticedate
  FROM bbs
 WHERE noticeyn = 'Y'
   AND noticedate >= DATE(DATE_ADD(NOW(), INTERVAL 1 DAY))
 ORDER BY cid DESC
 LIMIT 1
)
 UNION ALL
(
SELECT cid, title, contents, noticeyn, noticedate
  FROM bbs a
 WHERE cid  != (SELECT cid
                  FROM bbs
                 WHERE noticeyn = 'Y'
                   AND noticedate >= DATE(DATE_ADD(NOW(), INTERVAL 1 DAY))
                 ORDER BY cid DESC
                 LIMIT 1
                )
 ORDER BY cid DESC
 LIMIT 0, 6
)
 LIMIT 0, 6
;