Help with sorting

Fredrik M 146 Reputation points

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.


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

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

    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 = and type = 'Full') then 1 else 2 end), 
        case type when 'Full' then 1 else 2 end,
    -- 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

    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