我想要依照固定排序 Left join最相符的Appoinment

狀況是要列出 各user前/後一筆Appointment，且符合某些條件下必須優先顯示
因為是混合式條件，無法個別判斷後JOIN，故使用ORDER，再用DISTINCT+GROUP
例如: 該user有多筆時 status = 2 優先, status = 1, 再依時間...各種狀態 排序，但只需抓第一筆相符之後，再做Left JOIN

而 DISTINCT+GROUP去抓第一筆的問題在 如何在GROUP後保持排序
用最佳解法做變化，可以有效達成效果

也希望有其他能直接拉出所需資料的方法

==================================================

因為每個Table都很大，所以我另外抓出Filter並排序，到這邊資料出來的狀況和排序都符合預期。
但想要抓出各資料的第一筆時，卻會變回系統排序的第一筆...

基本上order都index過...
希望版上大大可以提供一點建議>"<

主程式為下

SELECT User.*, NextAppointment.*, LastAppointment.*
								 
FROM( User )
LEFT JOIN(
	SELECT *, COUNT(DISTINCT( user_id ))
		FROM( SELECT Appointment.id, Appointment.user_id
              FROM( Appointment )
			  WHERE Appointment.from_time>='2020-05-26'
                AND Appointment.status=1
			  ORDER BY Appointment.from_time, Appointment.confirmed=1 DESC, Appointment.id DESC
			)NextAppointment_sub
						
		GROUP BY user_id 
	)NextAppointment	ON NextAppointment.user_id = User.id

LEFT JOIN(
	SELECT *, COUNT(DISTINCT( user_id ))
		FROM( SELECT Appointment.id, Appointment.user_id
              FROM( Appointment )
			  WHERE Appointment.from_time<='2020-05-26'
			  ORDER BY Appointment.status=2 DESC, Appointment.from_time DESC, Appointment.confirmed=1 DESC, Appointment.status=1 DESC, Appointment.id DESC
			)LastAppointment_sub
						
		GROUP BY user_id 
	)LastAppointment	ON LastAppointment.user_id = User.id AND LastAppointment.from_time >= User.FirstRequest
	

在Left join 的子查詢中...
抓取先前的Appointment，並以有狀態優先、時間... 等依序排列後，使用 DISTINCT +GROUP 抓取各user的第一筆符合資料，會變回系統排序


SELECT *, COUNT(DISTINCT( user_id ))
    FROM( SELECT Appointment.id, Appointment.user_id
          FROM( Appointment )
          WHERE Appointment.from_time<='2020-05-26'
          ORDER BY Appointment.status=2 DESC, Appointment.from_time DESC, Appointment.confirmed=1 DESC, Appointment.status=1 DESC, Appointment.id DESC
        )LastAppointment_sub

    GROUP BY user_id