Harry Harry - 4 months ago 8
SQL Question

SQL: Order by column, then by substring mix asc and desc

Example: In my warehouse I have rows and each row has 5 shelves.

I'm walking through the aisle and have row 1 to my right and row 2 to my left, I start picking an order from row 1 going up from 1 to 5 then I go to row 2 down from 5 to 1.

I have a column in database as follows:

01-001-A-01
01-002-A-02
01-003-A-03
01-004-A-01
01-005-A-03
02-001-A-01
02-002-A-02
02-003-A-03
02-004-A-01
02-005-A-03


The first 2 letters are the rows and the next 3 letters are the shelves, I would like to achieve that the second row should be in DESC order while the first (and third etc) should be in ASC order. As follows:

01-001-A-01
01-002-A-02
01-003-A-03
01-004-A-01
01-005-A-03
02-005-A-03
02-004-A-01
02-003-A-03
02-002-A-02
02-001-A-01


So basically each odd row will be ASC and each even will be DESC.

Answer

Demo Here

select *
from #temp
order
by substring(id,1,2),
case
when substring(id,1,2)%2=0 then row_number() over (partition by substring(id,1,2) order by  SUBSTRING(id,4,3) desc)
else row_number() over (partition by substring(id,1,2) order by  SUBSTRING(id,4,3) asc)
end