Sql query needs to sort on multiple date columns together

Question

I have a table with three date fields, a start date, mid-term date, and end date. I would like to create a single query to get the most recent activity from the table. Activity in this case being when the date fields are updated.

Without having to write 3 separate queries and then combining the values in my code to get the 10 most recent activities, can I do this in one query. So right now I have

    SELECT TOP 10 * FROM core_table 
    ORDER BY [start_date] Desc

    SELECT TOP 10 * FROM core_table 
    ORDER BY [process_date] Desc

    SELECT TOP 10 * FROM core_table 
    ORDER BY [archive_date] Desc

So I would want to pull the results of those three queries together to get the top 10 entries based on all three dates.

Answer 1

based on answer given by Itiong_sh, which is not exactly the same : you can do it in ORDER BY

select top 10 * from core_table
order by
  CASE
      WHEN start_date >= process_date AND start_date >= archive_date 
          THEN  start_date
      WHEN process_date >= archive_date 
          THEN  process_date
      ELSE  archive_date
  END
 DESC