Sort by multiple columns but different crietria

Question

I have a table in SQL SERVER from which i want to select the records using order by but i am having different scenario. Please have a look at below table.

EmployeeCode | EmployeeArea
    001      |     8568
    002      |     4549
    004      |     4549
    005      |     8568
    003      |     4549
    006      |     7777
    010      |     4549
    007      |     8568
    008      |     7777
    009      |     4549

As shown above i have two fields Employee Code and Employee Area. Now, What i want to do is Sort Records by Employee Codes first and then EmployeeArea but not in standard Way. Please see below desired o/p for better understanding

  EmployeeCode   | EmployeeArea
        001      |     8568
        005      |     8568
        007      |     8568
        002      |     4549
        003      |     4549
        004      |     4549
        009      |     4549
        010      |     4549
        006      |     7777
        008      |     7777

As shown above first sort is on employeeCode and according to that, first employee(ex 001)'s Employee Area(8568) all employees with areacode 01 will be placed together and it will placed in asc order by employee code. I tired using multiple ways like group by, CTE, temporary tables but cant get desired output. Any help will be highly appreciated.

Answer 1

Seems like what you need is a windowed MIN in the ORDER BY:

SELECT *
FROM (VALUES('001',8568),
            ('002',4549),
            ('004',4549),
            ('005',8568),
            ('003',4549),
            ('006',7777),
            ('010',4549),
            ('007',8568),
            ('008',7777),
            ('009',4549))V(EmployeeCode,EmployeeArea)
ORDER BY MIN(EmployeeCode) OVER (PARTITION BY EmployeeArea),
         EmployeeCode,
         EmployeeArea;