How to fetch the rows with their predefine order in SQL Server?

Question

I have a SQL Server table like this:

MenuID    MenuName           MenuColor
---------------------------------------
10         Daily Tickets     Gray
15         Kids Ticket       Dark Pink
20         Group Discount    Dark Ash
11         Discount ticket   Brown
17         Referral Ticket   Beige
22         Frequent visitor  Musturd
27         Annual Pass       sky blue
25         Kids Pass         Pink
24         free Ticket       Yellow

This table has lot of records and more columns too..

Desired result - first four Menus should be ordered with pre-defined order (which I mentioned in my trial query) and remaining should be ordered ASC on MenuName column

Desired result set:

MenuID    MenuName           MenuColor
---------------------------------------
10         Daily Tickets     Gray
27         Annual Pass       sky blue
22         Frequent visitor  Musturd
20         Group Discount    Dark Ash
11         Discount ticket   Brown
24         free Ticket       Yellow
25         Kids Pass         Pink
15         Kids Ticket       Dark Pink
17         Referral Ticket   Beige

This is the query I tried for this:

 SELECT * 
 FROM tMenus m
 ORDER BY 
     (CASE m.MenuName
         WHEN 'Daily Tickets' THEN 1
         WHEN 'Annual Pass'  THEN 2
         WHEN 'Frequent visitor' THEN 3
         WHEN 'Group Discount' THEN 4  
      END), m.MenuName ASC;

However, this is not returning the result that I want. Please correct me where I am wrong.

Thanks

Answer 1

Adding "DisplayOrder" to the actual table...

IF OBJECT_ID('tempdb..#Menue', 'U') IS NOT NULL 
DROP TABLE #Menue;

CREATE TABLE #Menue (
    MenuID INT NOT NULL PRIMARY KEY,
    MenuName VARCHAR(30) NOT NULL,
    MenuColor VARCHAR(10) NOT NULL,
    DisplayOrder INT NOT NULL 
    );

INSERT #Menue(MenuID, MenuName, MenuColor, DisplayOrder) VALUES 
    (10,'Daily Tickets', 'Gray', 100),
    (15,'Kids Ticket', 'Dark Pink', 800),
    (20,'Group Discount', 'Dark Ash', 400),
    (11,'Discount ticket', 'Brown', 500),
    (17,'Referral Ticket', 'Beige', 900),
    (22,'Frequent visitor', 'Musturd', 300),
    (27,'Annual Pass', 'sky blue', 200),
    (25,'Kids Pass', 'Pink', 700),
    (24,'free Ticket', 'Yellow', 600);
    -- Note: I'm leaving gaps in the DisplayOrder values.
    --  This makes it easy to add new values and set their 
    --  values w/o having to adjust existing values.

SELECT 
    m.MenuID,
    m.MenuName,
    m.MenuColor
FROM 
    #Menue m
ORDER BY 
    m.DisplayOrder;

Edited answer...

IF OBJECT_ID('tempdb..#MenueDisplayOrder', 'U') IS NOT NULL 
DROP TABLE #MenueDisplayOrder;

CREATE TABLE #MenueDisplayOrder (
    MenueID INT NOT NULL, --add FK to Menues table
    DisplayTypeID INT NOT NULL, --add FK to available Types table
    DisplayOrder INT NOT NULL 
    PRIMARY KEY CLUSTERED (DisplayTypeID, MenueID)
    );

    INSERT #MenueDisplayOrder (MenueID, DisplayTypeID, DisplayOrder) VALUES 
    (10, 1, 100), (11, 1, 500), (15, 1, 800), (17, 1, 900), (20, 1, 400),
    (22, 1, 300), (24, 1, 600), (25, 1, 700), (27, 1, 200), 
    (27, 2, 100), (25, 2, 500), (24, 2, 800), (20, 2, 900), (17, 2, 400),
    (22, 2, 300), (15, 2, 600), (11, 2, 700), (10, 2, 200),
    (15, 3, 100), (11, 3, 500), (10, 3, 800), (22, 3, 900), (24, 3, 400),
    (17, 3, 300), (20, 3, 600), (27, 3, 700), (25, 3, 200);

SELECT 
    m.MenuID,
    m.MenuName,
    m.MenuColor
FROM 
    #Menue m
    JOIN #MenueDisplayOrder mdo
        ON m.MenuID = mdo.MenueID
WHERE 
    mdo.DisplayTypeID = 2 -- alter this value to change the display order.
ORDER BY 
    mdo.DisplayOrder;

Answer 2

Perhaps you just need an else:

 ORDER BY (CASE m.MenuName
             WHEN 'Daily Tickets' THEN 1
             WHEN 'Annual Pass'  THEN 2
             WHEN 'Frequent visitor'  THEN 3
             WHEN 'Group Discount' THEN 4  
             ELSE 5
          END) , m.MenuName ASC;