按自定义顺序排序多个字段，最后一个空字段

Question

I am editing this question as the original was quite poorly worded, and had incorrect information that I didn't see until after it was answered, which understandably caused confusion. Since I can't delete it, I will attempt to edit it the best I can.

I have 3 fields in a large table that store information pertaining to a timeline, and I would like to apply custom ordering to all of them.

The "Date" column holds either a year OR an era name. There are 3 options for the era name in this example: Early Era, Mid Era, and Late Era. If a year is present in the "Date" column, in the same row the "Era" and/or "Era2" columns may optionally have an era name. There will never be a value in the "Era2" column without there also being a value in the "Era" column.

I would like to perform the following sort:

• I would like to put rows with all three columns empty at the very bottom.

• I would then like all the "Date" columns that have only a year -- with "Era" and "Era2" columns in that row empty -- toward the bottom. The year may be sorted in ascending order.

• Then, I would like to order any column with an era name ("Date", "Era", or "Era2" column) with a predefined custom order (see below). Rows with a year defined in the "Date" and a value in the "Era" or "Era2" columns will sort before "Date" columns which contain only an era name.

Example of Predefined Order:

 ("Early Era","Mid Era","Late Era")

Current Unsorted Table:

Date           | Era           | Era2
-------------- |-------------- |-------------- 
         1991  | Early Era     |
         1992  | Early Era     |
    Early Era  |               |
         1999  | Mid Era       | Late Era
         1999  | Mid Era       | Early Era
      Mid Era  |               |  
     Late Era  |               |  
    Early Era  |               |  
               | Mid Era       |  
               | Late Era      |        
         2009  |               |    
     Late Era  |               |            
         2014  | Late Era      |
               |               |              
         2015  |               |         
         1992  |               |            
         1999  |               |            
         1992  | Early Era     |             

Desired Result After Sort:

Date           | Era           | Era2
-------------- |-------------- |-------------- 
         1991  | Early Era     |
         1992  | Early Era     |
         1992  | Early Era     |       
    Early Era  |               |        
    Early Era  |               |
         1999  | Mid Era       | Early Era
         1999  | Mid Era       | Late Era
      Mid Era  |               |  
               | Mid Era       |  
         2014  | Late Era      |
     Late Era  |               |
     Late Era  |               |    
               | Late Era      |  
         1992  |               |  
         1999  |               |                     
         2009  |               |              
         2015  |               |         
               |               |                    

Current code which doesn't fully work, but it's mostly there:

SELECT *
FROM yourTable
ORDER BY
        CASE WHEN Date LIKE '%Era%' OR Era LIKE '%Era%' THEN 0 ELSE 1 END,
        CASE WHEN Date != '' THEN 0 ELSE 1 END,
        FIELD(CASE WHEN Era = '' THEN Date ELSE Era END, 'Late Era','Mid Era','Early Era') DESC, 
        CASE WHEN Era LIKE '%Era%'  THEN 0
             WHEN Date LIKE '%Era%' THEN 1 END;

Undesired Order With Current Code, After Sort

Date           | Era           | Era2
-------------- |-------------- |-------------- 
         1991  | Early Era     |
         1992  | Early Era     |
         1992  | Early Era     |       
    Early Era  |               |        
    Early Era  |               |
         1999  | Mid Era       | Late Era
         1999  | Mid Era       | Early Era
      Mid Era  |               |  
         2014  | Late Era      |
     Late Era  |               |  
     Late Era  |               |  
               | Mid Era       |  
               | Late Era      |        
         2009  |               |              
         2015  |               |         
         1992  |               |            
         1999  |               |           
               |               |              

Live example: http://rextester.com/CAMA42792

Note that the era names in the "Era2" column are not sorted correctly. Also, the era names in the "Era" column (those with an empty "Date" column) are still not sorted correctly -- they should be grouped with like era names. Thirdly, the years at the very bottom are in the correct position, they are just not in ascending order. Hope that makes sense, and please let me know if there's anything in need of further explanation, I understand it's a bit convoluted. Thank you in advance for any help.

Edit - This is a very different question than the original, but hopefully it clarifies and corrects the issues present in my initial version. If the mods deem it best, they may delete this question and I will be happy to create a new one.

Answer 1

You can try the following query:

SELECT *
FROM yourTable
ORDER BY
    CASE WHEN Date LIKE '%Era%' OR Era LIKE '%Era%' THEN 0 ELSE 1 END,
    CASE WHEN Date IS NOT NULL THEN 0 ELSE 1 END,
    FIELD(COALESCE(Era, Date), 'Late Era','Mid Era','Early Era') DESC, 
    CASE WHEN Era LIKE '%Era%'  THEN 0
         WHEN Date LIKE '%Era%' THEN 1 END;

This query is based around what I saw in your expected output. That is, it selects the following in order of precedence:

• records where Date or Era have the text Era appear before records which do not have this text
• records having a NULL date come after those whose date is not NULL
• then order by early, mid, late, NULL (you almost already have this)
• finally put records with a Date era after those with an era in the Era column

Demo here:

Rextester