Help with sorting

Question

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?

Answer 1

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.

Answer 2

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