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:
| ID | Ring_Size | W_Size | Purity | F_Size |
|---|---|---|---|---|
| D46320 | 16 | 3.4 | 38 | 94 |
| F15555 | 10 | 10.1 | 4 | 81 |
| B45555 | 10 | 14.01 | 4 | 83.1 |
| E64444 | 10 | 14.0 | 2 | 84.2 |
| E64444 | 10 | 14.81 | 2 | 84.2 |
| B45445 | 10 | 15.1 | 2 | 87.1 |
| B45475 | 10 | 15.1 | 4 | 87.1 |
| E66744 | 10 | 15.1 | 4 | 87.1 |
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:
| ID | Ring_Size | W_Size | Purity | F_Size |
|---|---|---|---|---|
| D46320 | 16 | 3.4 | 38 | 94 |
| F15555 | 10 | 10.1 | 4 | 81 |
| B45555 | 10 | 14.01 | 4 | 83.1 |
| E64444 | 10 | 14.0 | 2 | 84.2 |
| E64444 | 10 | 14.81 | 2 | 84.2 |
| B45445 | 10 | 15.1 | 4 | 87.1 |
| B45475 | 10 | 15.1 | 4 | 87.1 |
| E66744 | 10 | 15.1 | 2 | 87.1 |
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:
| ID | Ring_Size | W_Size | Purity | F_Size |
|---|---|---|---|---|
| D46320 | 16 | 3.4 | 38 | 94 |
| F15555 | 10 | 10.1 | 4 | 81 |
| E64444 | 10 | 14.0 | 2 | 84.2 |
| B45555 | 10 | 14.01 | 4 | 83.1 |
| E64444 | 10 | 14.81 | 2 | 84.2 |
| E66744 | 10 | 15.1 | 2 | 87.1 |
| B45445 | 10 | 15.1 | 4 | 87.1 |
| B45475 | 10 | 15.1 | 4 | 87.1 |
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.
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.