SQL ORDER BY - save a column with the same value, grouped

Question

I have a lot of difficulties in expressing in words what I want (this heading is the best I could think of), it’s easier with the diagram, but I will try to explain myself first.

I want to order a request using this rule:

• Group the query by the Group column.
• Place the “Primary” of each group on top of its group.
• If the strings have the same "Name" as the primary, place it immediately after their primary.
• Put the rest of the lines by their names

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

I read a little about RANK () and ROW_NUMBER () , tried them, but I was not able to create the output that I want. I begin to doubt if this is possible.

Anyway, here is a piece of SQL to test it. Any help is appreciated. If you can find the best conditions to describe this, feel free to fix it.

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 result:

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

I want 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

according to the comment

You can do a self-study and check if the name matches the primary, and if so assign a value, for example. 1 else 0, then use this column in its order, 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

Answer 2

max, col_name col_primary = 1 case order by .

SELECT * FROM (
SELECT
     t.*,max(case when col_primary=1 then col_name end) over(partition by col_group) as prim_col_name
FROM 
    #TEMP t
) t
ORDER BY 
    COL_GROUP, 
    COL_PRIMARY DESC, 
    case when COL_NAME = prim_col_name then 1 else 2 end,
    COL_NAME

, col_primary = 1 col_group.

Answer 3

, :

SELECT 
    *
FROM #TEMP o
ORDER BY COL_GROUP, COL_PRIMARY DESC, CASE WHEN COL_NAME = (SELECT COL_Name FROM #Temp i WHERE i.COL_PRimary = 1 AND i.Col_Group = o.Col_Group) THEN 0 ELSE 1 END, COL_NAME;

, , , : " , , 0 else 1". , , , CTE , . , .

Answer 4

Using the left join in the table itself, make sure that you don’t lose records if there is no main element in the group (Maby is not a problem).

It will also simplify the “ order ” by simply adding the associated column T2.COL_NAME (which will not be empty if COL_NAME matches the main one).

SELECT
         T1.*
FROM 
        #TEMP T1
        left join #TEMP T2 on T2.COL_GROUP = T1.COL_GROUP and T2.COL_NAME  = T1.COL_NAME and T2.COL_PRIMARY = 1
ORDER BY 
        T1.COL_GROUP, 
        T1.COL_PRIMARY DESC, 
        T2.COL_NAME DESC,
        T1.COL_NAME