0

Each ship docks at a port. If too much ships arrive at a single port the crew gets overwhelmed.

How to return a result set so a port does not get overloaded? One possible solution and set based result would be:

SELECT shipnr, portnr FROM (
SELECT shipnr, portnr, DENSE_RANK() OVER(PARTITION BY portnr ORDER BY shipnr) AS N FROM ships
) AS a
ORDER BY N, portnr

However, this is not what I am looking for since at the end we get three ships arriving at F. Below is an example I did by hand of the result set I am looking for:

1   A
6   B
10  C
11  D
13  E
16  F
20  F
2   A
7   B
12  D
14  E
17  F
21  F
3   A
8   B
15  E
18  F
22  F
4   A
9   B
19  F
5   A

Since there are many port F it should return in groups of 2 so that specific port is not overwhelmed.

2

Ok, here is my revised approach, using NTILE. It actually gives roughly the same as your proposed output.

declare @ships table
(
shipnr int,
portnr varchar(50)
);

insert into @ships (shipnr, portnr)
values (1, 'A'), (2, 'A'), (3, 'A'), (4, 'A'), (5, 'A'), (6, 'B'), (7, 'B'), (8, 'B'), (9, 'B'), (10, 'C'),
(11,'D'), (12, 'D'), (13, 'E'), (14, 'E'), (15, 'E'), (16, 'F'), (17, 'F'), (18, 'F'), (19, 'F'), (20, 'F') , (21, 'F') , (22, 'F') 

DECLARE @PortNumber INT = (SELECT COUNT(DISTINCT portnr) FROM @ships) - 1

;WITH CTE_Ships AS
    (
    SELECT S.shipnr
        , S.portnr
        , NT = NTILE(@PortNumber) OVER (PARTITION BY portnr ORDER BY (portnr))
    FROM @ships AS S
    )

SELECT * FROM CTE_Ships ORDER BY NT, portnr
|improve this answer|||||
  • You have reassigned ports to the ships, this is not possible. The original port assigned to each ship is set in stone. The only thing that can change is the order in which the result set is returned – tutu Jan 10 at 13:39
  • I see, I misunderstood your requirements. Could you put together a sample output (if you were doing the ordering by hand, what would you do with the fact that port F has 7 ships? – Jonathan Fite Jan 10 at 13:59
  • I added an example by hand in my edited post – tutu Jan 10 at 14:06
  • Thanks! I think my revised approach should work for you, using NTILE. – Jonathan Fite Jan 10 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.