I'm trying to do a very specific ordering with a SQL server. I have tried two different queries, and they're close, but they create an issue that I can't completely fix.

It'll be easier if I show the results I want:

This focuses on ring size in descending order. Then it pairs w_size together in ascending order while also keeping ID's together. Then, finally, when there are matches, it would order the matches by purity ascending.

I can only achieve all but one of these conditions. I keep trading something off.

I can achieve everything except the final ordering by ascending purity with this code:

SELECT 
    ID, Ring_Size, 
    RIGHT(Gspec, CHARINDEX(' ', REVERSE(Gspec)) - 1) AS W_SIZE,
    LEFT(Gspec, CHARINDEX(' ', Gspec) - 1) AS Purity, F_Size 
FROM 
    JewelColl
ORDER BY 
    Ring_Size DESC, 
    RIGHT(Gspec, CHARINDEX(' ', REVERSE(Gspec)) - 1)   ID,
    CAST(LEFT(Gspec, CHARINDEX(' ', Gspec) - 1) AS INT) 

Which will result in:

You'll notice the last three rows for purity had the order changed to 4, 4, 2. Which is purity desc.

I can fix that with another query, but it no longer keeps ID's together:

SELECT 
    ID, Ring_Size, 
    RIGHT(Gspec, CHARINDEX(' ', REVERSE(Gspec)) - 1) AS W_SIZE,
    LEFT(Gspec, CHARINDEX(' ', Gspec) - 1) AS Purity, F_Size 
FROM 
    JewelColl
ORDER BY 
    Ring_Size DESC, 
    RIGHT(Gspec, CHARINDEX(' ', REVERSE(Gspec)) - 1),
    CAST(LEFT(Gspec, CHARINDEX(' ', Gspec) - 1) AS INT), ID 

ID placement is the difference.

Here are the results:

This solves the purity ascending order but it splits up ID's as noted by the order of:

E64444,B45555,E64444

Is there a way to keep the purity ascending order, ID's together, all while still matching W_Size?

Thank you.

CodePudding user response：

You have contradicting requirements in w_size in ascending order while also keeping ID's together, because the same ID may have both a smaller and a larger w_size than another ID. We can get around it by assigning a 'group' W_SIZE. I used MAX_W_SIZE here:

with MyTbl as (
select ID,  Ring_Size, W_Size,  Purity, F_Size, Max_W_Size=max(W_Size) over (partition by ID)
from (values 
 ('D46320', 16, 3.4     ,38 ,94   )
,('E64444', 10, 14.81   ,2  ,84.2 )
,('F15555', 10, 10.1    ,4  ,81   )
,('E64444', 10, 14.0    ,2  ,84.2 )
,('B45445', 10, 15.1    ,2  ,87.1 )
,('B45475', 10, 15.1    ,4  ,87.1 )
,('B45555', 10, 14.01   ,4  ,83.1 )
,('E66744', 10, 15.1    ,4  ,87.1 )
) T(ID, Ring_Size, W_Size,  Purity, F_Size)
)
select * from MyTbl 
order by 
  ring_size desc
, Max_W_Size
, ID
, W_Size
, Purity

I used your results as data; you can replace the VALUES part with your query.