Order by alternating rows?

This should be easy, but I'm not wrapping my mind around it. I have a dataset that looks like this. Just two columns - one with the Shipping Carrier & one with the Tracking number. This query is then used by another software to call the respective APIs & get the tracking status.

However, the goal is not to overload any one carrier at a given time. So as much as the dataset allows, I would like to alternate the rows. FedEx, UPS, USPS, FedEx, UPS, USPS, FedEx, UPS, USPS, etc.

I can Order By random & that is better than nothing, but I can't figure out how to order by alternating rows. Thanks!

ShippingCarrier TrackingNumber
FedEx 391601406511
FedEx 391601608423
UPS 1Z01222391601809514
FedEx 391601610361
FedEx 391601611774
USPS 9455111899562470325312

To do this - you just need to assign a row number to each instance and then order by the row number and the shipping carrier. Here is an example (notice how I built the sample table and data):

Declare @shippingTable Table (ShippingCarrier varchar(20), TrackingNumber varchar(30))

 Insert Into @shippingTable (ShippingCarrier, TrackingNumber)
 Values ('FedEx', '391601406511')
      , ('FedEx', '391601608423')
      , ('UPS', '1Z01222391601809514')
      , ('FedEx', '391601610361')
      , ('FedEx', '391601611774')
      , ('USPS', '9455111899562470325312')
      , ('USPS', '9455111899562470325313')
      , ('USPS', '9455111899562470325314')
      , ('USPS', '9455111899562470325315')
      , ('UPS', '1Z01222391601809514')
      , ('UPS', '1Z01222391601809555')
      , ('UPS', '1Z01222391601809566');

   With trackingData
     As (
 Select st.ShippingCarrier
      , st.TrackingNumber
      , rn = row_number() over(Partition By st.ShippingCarrier Order By st.TrackingNumber)
   From @shippingTable st
 Select *
   From trackingData
  Order By
      , ShippingCarrier;

If you provide the sample table and data - you would get a quicker and faster response. It would also help if you provided the expected results.


Thanks! No need because the query worked perfectly from the first shot. This is great!


You missed the point. Of course there's no need on this post because, as you said, the good deed has already been done. @jeffw8713 is suggesting it for your future posts to help you get such good answers in the future.