Help with sorting

Fredrik M 146 Reputation points
2022-02-09T08:32:16.393+00:00

Ok, I am probably stupid here, but I can't find a way to do this.

For example, I have 5 rows. I want the names to be combined in the sorting, but I want the Type "Full" to be the top name combined rows. I have tried with the window function RANK but I don't find a proper way to do it.

172506-sorting.jpg

You see the desired sort order to the right. Does it make any sense?

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 111.5K Reputation points
    2022-02-09T10:02:30.43+00:00

    It seems that you want to move the names that are 'Full' to the top of the list. Try this query:

    select type, name
    from Table t
    order by 
        (case when exists (select * from Table where name = t.name and type = 'Full') then 1 else 2 end), 
        name, 
        case type when 'Full' then 1 else 2 end,
        type
    
    -- Result:
    --   type   name
    --   Full    Jim 
    --   Half    Jim 
    --   Full    Nick
    --   Half    Joe 
    --   Half    Joe 
    

    If there are no other types, then you can simplify it.


1 additional answer

Sort by: Most helpful
  1. Olaf Helper 40,576 Reputation points
    2022-02-09T08:42:34.51+00:00

    You can use a CASE condition in the ORDER BY clause to get the wanted order:

    SELECT *
    FROM yourTable
    ORDER BY Name,
             CASE WHEN Type = 'FULL' 
                  THEN 0
                  ELSE 1 END