I want to select all the rows of carInventory table but have the brands which has a car in some sort of blue to be first. After, the cars in each brand sorts by year.

I was trying to alter the solution that was given in this post Take precedence on a specific value from a table but I can't seem to keep the brands together after I figure out the rank

SELECT
 *, RANK() over(
    partition by colBrand
    order by case when colColor like '%blue%' then 1 else 0 end
    ) RANK FROM inventoryTable Order By Rank, colBrand, colYear

Here's what the tables should look like. Starting Table

Search Result

CodePudding user response：

Firstly, the auto industry uses the terms Make and Model to refer to what you call Brand and Make. Using your own terminology will be confusing to many.

I think if you write your order logic more precisely and completely, you will more easily find a path to a solution. And it helps to be consistent. What is "ColBrand"? ColYear? IMO you do everyone a disservice by prefacing column names with a redundant prefix. And here "col" refers to "column"? Just don't!

So it seems you want to sort by <brands with blue vehicles / brands without>, brand, year descending. Notice that Make is not included in your ordering. And notice that your Taos has year 2015 in the table but swaps that year for another in the desired output. This is one reason why you should post a script - helps to avoid typos like that and encourages others to help you.

So here is another way to accomplish that. The CTE selects all brands that have blue colors. You simply outer join the actual table to the CTE to know if the brand satisfies the blue condition. Use that knowledge in the CASE expression in the ORDER BY clause.

with blubr as (select distinct brand from @inventory where color like '%blue%')
select inv.brand, inv.make, inv.color, inv.year
  from @inventory as inv left join blubr on inv.brand = blubr.brand
order by case when blubr.brand is not null then 0 else 1 end, 
   inv.brand, inv.year desc
;

fiddle to demonstrate Note there is a flaw in the logic of the prior answer. I've added a row to illustrate it. Do you see it? It is an easy fix to that query. Is this query better? Not really but it hopefully helps you think of different approaches to achieving the same goal.

CodePudding user response：

Interesting, maybe below query can do what you need

I also made a demo fiddle

 select *
from inventoryTable  i
order by 
case when exists ( select 1 from inventoryTable t 
          where t.colbrand=i.colbrand and t.colcolor like '%blue%') then 9999 
     else colYear end desc,colBrand asc, colyear desc