Order by alternating rows?

Transact-SQL
1

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
2

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
        rn
      , 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.

3 Likes
3

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

4

@SergioM,

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.

2 Likes