SQL Server Custom ORDER BY – How to Implement in SSMS

If I do a Paste Special, I get two options, Text and Unicode, and selecting Text seems to work properly, where as Unicode does not. as per comment by @JonSeigel

All you need to do is ORDER BY startDate ASC first, then featured DESC:

SELECT 
    a.* --,
    -- c.id as catid, 
    -- c.cat_name 
FROM eventlisting a 
-- LEFT JOIN categories c ON a.cat_num=c.id 
WHERE a.startDate >= '2015-08-21'
AND a.endDate <= '2016-08-21' 
AND a.status=1 
GROUP BY a.id 
ORDER BY a.startDate ASC,
         a.featured DESC
LIMIT 0, 50;

Note: I've commented the categories table because you didn't specified which is its structure.

Test:

mysql> SELECT  a.*            FROM eventlisting a   WHERE a.startDate >= '2015-08-21' AND a.endDate <= '2016-08-21'  AND a.status=1  GROUP BY a.id  ORDER BY a.startDate ASC,  a.featured DESC LIMIT 0, 50;
+----+----------+--------+------------+------------+
| id | featured | status | startDate  | endDate    |
+----+----------+--------+------------+------------+
|  3 |        1 |      1 | 2015-08-21 | 2015-08-25 |
|  1 |        0 |      1 | 2015-08-21 | 2015-08-21 |
|  2 |        0 |      1 | 2015-08-21 | 2015-08-22 |
|  5 |        1 |      1 | 2015-08-22 | 2015-08-27 |
|  7 |        1 |      1 | 2015-08-22 | 2015-08-31 |
|  6 |        0 |      1 | 2015-08-22 | 2015-08-31 |
+----+----------+--------+------------+------------+
6 rows in set (0.00 sec)

mysql> 

Test it in this Fiddle.