SQL ORDER BY - Keep column with same value grouped

Question

I have a lot of difficulties to express in words what I want (this title is the best I could come with), it's easier with a schema, but I'll first try to explain myself.

I want to order by a query using this rule:

1. Group the query by their "Group" column.
2. Place the "Primary" of each group on top of their group.
3. If a rows have the same "Name" as the primary, place it just after their primary.
4. Place the rest of the rows by their names

Points 1, 2 and 4 are trivial. A simple ORDER BY to the trick. But I have never seen a query with point 3.

I've read a bit about RANK() and ROW_NUMBER() functions, tried them out, but I haven't manage to create the output I want. I'm starting to question if it's even possible to do so.

Anyway, here's a chunk of SQL to test it out. Any help is appreciated. If you can find better terms to describe this, feel free to correct.

CREATE TABLE #TEMP
(
    COL_GROUP INT,
    COL_PRIMARY BIT,
    COL_NAME VARCHAR(3)
)

INSERT INTO 
    #TEMP 
VALUES
    (1,1,'AAA'),
    (2,0,'BBB'),
    (2,1,'BBB'),
    (1,0,'BBB'),
    (1,0,'AAA'),
    (2,0,'AAA')

SELECT
     *
FROM 
    #TEMP
ORDER BY 
    COL_GROUP, 
    COL_PRIMARY DESC, 
    COL_NAME

DROP TABLE #TEMP

This gives this output:

COL_GROUP   COL_PRIMARY   COL_NAME
=========   ===========   ========
1           1             AAA  
1           0             AAA
1           0             BBB
2           1             BBB
2           0             AAA
2           0             BBB

What I want is this output:

COL_GROUP   COL_PRIMARY   COL_NAME
=========   ===========   ========
1           1             AAA  
1           0             AAA
1           0             BBB
2           1             BBB
2           0             BBB       -- The ones with the same name as the primary first
2           0             AAA

Answer 1

solution as per comment

You could do a self-join and test if the name is equal to the primary, and if so assign the value of e.g. 1 else 0, then use that column in your order by, before the name column.

SELECT
     T1.*
FROM 
    #TEMP T1 JOIN #TEMP T2 
    ON T1.COL_GROUP=T2.COL_GROUP AND T2.COL_PRIMARY=1
ORDER BY 
    T1.COL_GROUP, 
    T1.COL_PRIMARY DESC,
    CASE WHEN T1.COL_NAME=T2.COL_NAME THEN 1 ELSE 0 END DESC,
    T1.COL_NAME

See demo here