CODEWITHSUNDEEP
sortingsql-server-2008-r2ssmsreverse
itstknecht
itstknecht

Reputation: 67

Custom Reverse Sorting in SQL Server 2008R

I have a need for a special custom 'reverse' sorting. I've done some searching, but haven't found an answer yet. Our warehouse bins are in this format: "A-05E". I made 2 lists of bins below. ASC_Order is just that. Picking_Order is the custom sort order I need to have printed on the Warehouse Picking Ticket. We need to change the sort order when the first digit of the Bin Name changes. (a letter, as shown below). 1) The first digit is always sorted as ASC. 2) The 2 number digits (position 3&4 in the Bin Name) are where the sorting needs to change. To start with they are sorted as ASC. When the first digit changes (from "A" to "B" below), their sort needs to change to DESC. When the first digit changes again (from "B" to "D" below) the sort needs changed back to ASC, and so forth for the remainder of the bins.

ASC_Order       Picking_Order
  A-05E            A-05E
  A-07A            A-07A
  B-00A            B-01D
  B-01D            B-00A      
  D-02B            D-02B
  D-07D            D-07D
  D-08H            D-08H
  D-09F            D-09F
  D-14G            D-14G
  M-00A            M-08A
  M-01F            M-04B
  M-04B            M-01F
  M-08A            M-00A

Upvotes: 0

Views: 31

Answers (1)

SQLChao
SQLChao

Reputation: 7847

I used substring and dense rank to assign number to groups of bins by the letter they start with. Then order that ASC by the first character of the string. Then further order it with the case statements. If the rank is an even number then order it desc. If the rank is an odd number then order it asc.

Here's a DEMO

SELECT *
FROM dbo.YourTable
ORDER BY 
  SUBSTRING(ASC_Order, 1, 1) ASC,
  CASE WHEN DENSE_RANK() OVER (ORDER BY SUBSTRING(ASC_Order, 1, 1)) % 2 = 0 THEN SUBSTRING(ASC_Order, 3, 2) END DESC,
  CASE WHEN DENSE_RANK() OVER (ORDER BY SUBSTRING(ASC_Order, 1, 1)) % 2 <> 0 THEN SUBSTRING(ASC_Order, 3, 2) END ASC

On a side note, I could have used LEFT(asc_order, 1) to get the first character but I already had substring on my mind because you have to get the middle 2 numbers.

EDIT

Thought about this a little more and the use of substring to get the middle 2 was unnecessary. Here's the shortened code where I removed the substring to get the middle 2 and also used LEFT to get the first character.

SELECT *
FROM dbo.YourTable
ORDER BY 
  LEFT(ASC_Order, 1) ASC,
  CASE WHEN DENSE_RANK() OVER (ORDER BY SUBSTRING(ASC_Order, 1, 1)) % 2 = 0 THEN ASC_Order END DESC,
  CASE WHEN DENSE_RANK() OVER (ORDER BY SUBSTRING(ASC_Order, 1, 1)) % 2 <> 0 THEN ASC_Order END ASC

Upvotes: 2

Related Questions