Sorting data based on specified value

Question

I have a table Containers with following data:

Name         | CapacityNormal | CapacityMax
-------------+----------------+------------
Container #1 |              2 |           2
Container #2 |              2 |           3
Container #3 |              2 |           4
Container #4 |              3 |           4
Container #5 |              3 |           5

And a variable Quantity. I want to sort containers with respect to quantity in this order:

containers whose capacity normal is greater than/equal to quantity
containers whose capacity max is greater than/equal to quantity
everything else

Note that:

Wasting normal slots is not desireable
Exceeding normal capacity incurs a pealty

Keeping the above in mind the ties must be broken according to these rules:

containers with smallest wasted normal capacity
containers with smallest penalty

If Quantity = 3 the expected output would be:

Name         | CapacityNormal | CapacityMax | Comment
-------------+----------------+-------------+-------------------------------------------
Container #4 |              3 |           4 | wastes 0 slots
Container #5 |              3 |           5 | wastes 1 slot
Container #2 |              2 |           3 | exceeds normal capacity, incurs 1 penalty
Container #3 |              2 |           4 | exceeds normal capacity, incurs 2 penalty
Container #1 |              2 |           2 | container cannot be used

Put another way, I want to choose best container for the job that does not exceed normal capacity or incurs smallest penalty. Here is sample data and test:

WITH Containers(Name, CapacityNormal, CapacityMax) AS (
    SELECT 'Container #1', 2, 2 UNION
    SELECT 'Container #2', 2, 3 UNION
    SELECT 'Container #3', 2, 4 UNION
    SELECT 'Container #4', 3, 4 UNION
    SELECT 'Container #5', 3, 5
), Tests(Quantity) AS (
    SELECT 1 UNION 
    SELECT 2 UNION 
    SELECT 3 UNION 
    SELECT 4 UNION 
    SELECT 5 UNION
    SELECT 6
)
SELECT CONCAT(Name, ' (', CapacityNormal, '/', CapacityMax, ')') AS Container, Quantity
FROM Containers
CROSS JOIN Tests
ORDER BY Quantity /* sort criteria goes here */

I have revised the requirements a little, sorry about that.

Salman Arshad
– Salman Arshad

2017-05-04 10:41:30 +00:00
Commented May 4, 2017 at 10:41 — Salman Arshad
– Salman Arshad, Commented May 4, 2017 at 10:41

TriV · Accepted Answer · 2017-05-04 12:15:35Z

1

You could use order by like this

;WITH Containers(Name, CapacityNormal, CapacityMax) AS (
   SELECT 'Container #1', 2, 2 UNION
   SELECT 'Container #2', 2, 3 UNION
   SELECT 'Container #3', 2, 4 UNION
   SELECT 'Container #4', 3, 4 UNION
   SELECT 'Container #5', 3, 5 UNION 
   SELECT 'Container #6', 4, 5
), Tests(Quantity) AS (
   SELECT 1 UNION 
   SELECT 2 UNION 
   SELECT 3 UNION 
   SELECT 4 UNION 
   SELECT 5 UNION
   SELECT 6
)
SELECT *
FROM Containers c
CROSS JOIN Tests t
ORDER BY Quantity,
       CASE WHEN c.CapacityNormal >= t.Quantity THEN 0 ELSE 1 END,
       CASE WHEN c.CapacityMax >= t.Quantity THEN 0 ELSE 1 END,     
       abs(c.CapacityNormal- quantity) ,
       abs(c.CapacityMax - quantity)

Note: Order by CASE WHEN c.CapacityNormal >= t.Quantity THEN 0 ELSE 1 END, CASE WHEN c.CapacityMax >= t.Quantity THEN 0 ELSE 1 END is equivalent to

containers whose capacity normal is greater than/equal to quantity
containers whose capacity max is greater than/equal to quantity

and then order by abs(c.CapacityNormal- quantity) , abs(c.CapacityMax - quantity) would guarantee

containers with smallest wasted normal capacity containers with
smallest penalty

edited May 4, 2017 at 12:15

answered May 4, 2017 at 11:01

TriV

5,1582 gold badges12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Salman Arshad Over a year ago

This breaks when I introduce a new case Container #6 (4/5)... it still chooses 3/5 which has penalty 2, it should have instead choose 4/5 which has 1 penalty.

TriV Over a year ago

What is your quantity you are testing?

Salman Arshad Over a year ago

that was tested against 5

score 0 · Accepted Answer · 2017-05-04 10:57:12Z

0

You could use an order by statement something like this:

order by 
    case 
        when capacitynormal >= quantity then 1
        when capacitymax >= quantity then 2
        else 3
    end,
    case 
        when capacitynormal >= quantity then capacitynormal - quantity
        else 9999 --set this to a number bigger than any capacity normal
    end,
    capacitymax - quantity

Note that I used a case statement for the second element, as well, because I think your question implies that if you have exceeded normal capacity you want to ignore that in the tiebreaking logic.

edited May 4, 2017 at 10:57

answered May 4, 2017 at 10:28

user1919238

1 Comment

Salman Arshad Over a year ago

For Quantity = 2 it can put Container #3 (2/4) at the top. ideally it should return Container #1 (2/2), Container #2 (2/3) and Container #3 (2/4) in that order.

Collectives™ on Stack Overflow

Sorting data based on specified value

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related